Month: April 2020

Searching every Procedures for a string.

To search every stored procedure in every database on every server (ranging from SQL Server 2005 to SQL Server 2016) for the string ‘QueryTraceOn’, I first registered every server within SSMS.

Right-clicking on the registered server folder, I chose ‘new query’ and ran ‘select 1’ to exclude from my list any server with issues.

Once I had an error free list, I ran this code (which took around 40 minutes) …

-- SearchProcs4String.sql

EXEC sp_MSforeachdb 'use ?
SELECT db_name() [Database], ROUTINE_SCHEMA + ''.'' 
+ ROUTINE_NAME [Proc]
FROM INFORMATION_SCHEMA.ROUTINES WITH (NOLOCK)
WHERE ROUTINE_DEFINITION LIKE ''%QUERYTRACEON%'';'

Getting away from Dedupe jobs

Duplicate data should ideally be stopped at the front end. However if a table already contains duplicate data you may want to bash out some code to clean it up. And schedule a SQL job to run the code regularly.

However, there are mechanisms baked right into SQL Server to manage this more efficiently (step away from scripting everything – devops 😉 )

True enough, you need to run code (once) to clean out all the current duplicates, but going forward a unique filtered index can keep them out.

For this particular project “duplicate data” meant that an CustID column should not contain a number already in that column if the Country was ‘GB’ and the PackageID was ‘5’.

Here is a simplified example of my solution …

-- ix_BlockDupCustIDs.sql

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1 (CustID INT, CountryCode CHAR(2), PackageID INT);

CREATE UNIQUE INDEX ix_BlockDupCustIDs 
ON #t1 (CustID) 
WHERE CountryCode = 'GB' AND PackageID = 5;

INSERT INTO #t1 VALUES (1, 'GB', 5) -- yes
INSERT INTO #t1 VALUES (2, 'GB', 5) -- yes
INSERT INTO #t1 VALUES (1, 'GB', 4) -- yes
INSERT INTO #t1 VALUES (1, 'US', 5) -- yes
--INSERT INTO #t1 VALUES (1, 'GB', 5) -- no, duplicate
--INSERT INTO #t1 VALUES (2, 'GB', 5), (3, 'IR', 1) -- no for both
--UPDATE #t1 SET PackageID = 5 WHERE PackageID = 4 -- nope

SELECT * FROM #t1;

How to run SQL Server within Docker

Here is my bare-bones walk-through for setting up SQL Server Developer Edition within Docker for those of us who don’t have time to learn docker right now.

1. I navigated to the Docker website and read about Docker Desktop before installing Docker Desktop for Windows (and creating an account) from my Windows 10 Pro laptop.

During installation I chose to use Windows containers by default, rather than Linux.

2. To find SQL Server Developer Edition, I reached out to the Docker Hub online library from my laptop with this command (typed into a Command-Prompt or Powershell window)

docker search mssql

… which brought back this list …

Annotation 2020-04-04 113431

The second item down on the list is from Microsoft, is in a Windows container, and is the Developer edition.

I copied the name into Notepad and composed this command around it to download, extract, and run SQL Server within a docker container …

docker run -d -p 1433:1433 -e sa_password=Pa55word# -e ACCEPT_EULA=Y --restart always -v C:\temp:c:\temp -v sqldata:c:\var\opt\mssql microsoft/mssql-server-windows-developer

Breaking down that command …

  • Docker = invoke Docker
  • Run = download, extract, and execute something from Docker Hub.
  • -d = detached mode. That is, run independent of the command line.
  • -p 1433:1433 = map external port 1433 to internal port 1433.
  • -e sa_password=Pa55word# = create an environment variable called sa_password containing the text “Pa55word#” (without the double quotes).
  • -e ACCEPT_EULA=Y = create another variable called ACCEPT_EULA containing the value “Y”.
  • –restart always = automatically start when Windows starts
  • -v c:\temp:c:\temp = map my laptop folder c:\temp to the docker folder c:\temp
  • -v sqldata:c:\var\opt\mssql = persist user databases
  • microsoft/mssql-server-windows-developer = this is the thing to “Run” (see above).

When complete (after 30 minutes or so) I connected via my regular laptop SSMS (IE: outside of the container) …

Annotation 2020-04-04 110654

… and added it to my registered servers as “Docker SQL Server Developer edition”

(Incidentally, that server name “dot” implies the local default instance on the default port. I could just as correctly have typed “localhost” or “localhost,1433”. Or from another laptop I would have used the IP address of the host laptop.)

Next I changed my Windows setting so the Docker icon would always be visible …

Capture

… so that on subsequent boots, I just need to wait for the icon to stop wobbling before connecting to the database via SSMS (and no need for any pesky Docker commands).

To import a backup I moved it to my laptop c:\temp and restored from there using SSMS.

“And finally” (as the late, great, Ronnie Corbett used to say), if after a while it all goes horribly wrong, as software tends to do, you can either uninstall docker and start this article again.

Or venture into the docker command-line to list (docker ps), stop (docker stop ~), then remove the SQL container (docker rm ~). Before using the green command above again.

More on pesky Docker commands soon.