Think and review before diving in head first

On a recent project assignment I was tasked to comb through a client database and target a specific field that resided within a single table. This field was powered by a free form text field on a .NET web application. Instead of separating the required bits of data into their own fields respectively everything was lumped into one NVARCHAR field. Definitely not an ideal situation. Nonetheless the task needed to be done. I spoke with the our Business Analyst and obtained the requirements.

So I fired up SSMS 2008R2 and started to formulate the query. That was my first mistake! I regressed back to my rookie days without really thinking it through and instead of really researching in-depth I started to ask for assistance (twitter hash tag: #sqlhelp to the rescue). Don’t get me wrong… asking for help isn’t terrible, it’s just that thinking it trough first really makes a significant difference. To make a long story short let me tell you what steps I took to make this move along smoother.

Step 1: Always review the data first

If I would have carefully inspected the data first I would have noticed the different ways the end users were entering the data. Then I could have easily constructed special conditions to target those specific instances. For example here is a short list of what the data kind of looked like:

  • Select on plan/Spec 283
  • Side Menu/Spec 118 Sht 30
  • Side Menu/Spec 474 Sht C1
  • Select on plan/Spec 283 Sheet 17
  • Select on Plan/Spec 278 Sht 44 Dtl A
  • Select on Plan/Spec 389 Sht 4 Bypass Piping
  • Select on Plan/Spec 408.1 Sht M-2
  • Site Piping Menu/Spec 408.1 Sht C-1 Plan
  • Surge Tank Menu/Spec 408.1 Sht M-4 Dtl B
  • Select on plan/Sheet 8 Detail C
  • Select from Side Menu/Sheet 7 Detail C
  • Menu from Discharge Relief Valve/Sheet 8 Detail F
  • Menu for Valve/Sheet 8 Detail A
  • Select on plan/Sheet 12 Detail C
  • Menu for Pressure Transmitter Cabinet/Sheet 20 Detail B
  • Menu for Suction Surge Tank 2/Sheet 15 Detail V

My task was to find the instance of SPEC, SHEET and SHT and grab the following numbers or char. As you can see there is only one instance of each one so that made it a bit easier.

Step 2: Identify the patterns

I took each keyword and began analyzing the data carefully:

SPEC
For SPEC the following three were always numeric (e.g. Spec 283) and there were a small handful the had three numeric followed by a period and another numeric digit (e.g Spec 408.1) so my options are grab the following four char or six to the right.

SHEET
For SHEET it was pretty easy. Always trailing SHEET was a numeric digit but the only difference was either a single digit (e.g. Sheet 2) or double digits (e.g. Sheet 12). So that means I can grab the following three char to the right and trim the left and right to satisfy this requirement.

SHT
The SHT on the other hand was a bit more difficult since it varied the most. This either had a trailing single numeric digit (e.g. Sht 2), double numeric digit (e.g. Sht 13) or mixed with alpha-numeric chars like: (e.g. Sht M-1 or Sht P8 or Sht P20).

Now that I can clearly see what patterns to look for I can begin to construct my query. Nothing like a little Function action using CHARINDEX mixed with a little dose of PATINDEX to assist in situations likes this. Of course there are many ways to approach this, but this worked for me and satisfied the requirements.

Step 3: Approach each requirement one at a time

This permitted me to concentrate on a specific goal until I was able to correctly return the desired output. Naturally I felt that attacking the easiest ones first would be best, but after reading the book, “Eat that Frog!” I learned to eat the biggest and ugliest frog first as it provides the greatest sense of accomplishment. So I started swinging at SHT. Then once that was crystal clear I moved onto SPEC and SHEET. I must mention that a fellow SQL buff by the name of Aaron Bertrand [ Twitter | Blog ] actually provided a very cool script that tackled the SPEC and covered all the basis. So Cheers to him!

Step 4: Piece everything together and test again

Once everything is working accordingly piece everything together and test it again. I cannot emphasize this enough. Q/A is necessary and must be done until everything comes out the way we need it to. Data is critical to any organization and we as data professionals know it better than anyone else. So test, test and retest. Everything worked out as needed and the results are positive.

Off the subject
If I didn’t mention this before #sqlhelp is extremely useful and there are many people that are extremely talented that are willing to lend you a helping hand. It’s a community thing and I love it! If twitter ain’t your thing that’s fine no biggie; however, if you are looking into it… do it! You won’t be sorry.

Advertisements

Dropping a subscriber database

Usually I can simply run an ALTER statement against the database and SET SINGLE_USER WITH NO_WAIT like so:

ALTER DATABASE MyDBName SET SINGLE_USER WITH NO_WAIT

but I ran into a problem with active processes that prevented me from switching to SINGLE_USER mode. So I executed the sp_Who2 stored procedure and identified the spids responsible and killed them!

EXEC sp_Who2

I essentially ran the kill command for every spid that was associated to the specific database. So you can see it was very inefficient and a great waste of time.

KILL 54
KILL 58

Now imagine if I had to do this will 200 subscribers… not a fun time!

Then I re-ran the stored procedure sp_Who2 again to make sure they were dead.

EXEC sp_Who2

This worked for me at the time, but later in the future I don’t want to deal with the hassles of running through all the steps involved. I turned to BOL and then I remembered I could easily query the sysprocesses and mock up a script but I didn’t really have the time to run through the whole Q/A process. So I searched the interwebs and stumbled upon the blog post [Script to drop all connections to a Database] by Tijo. It was exactly what I was looking for. So I borrowed it and merged it with some additional items that are specific to our clean up steps. In a nutshell here is the final outcome.

-- KILL ACTIVE PROCESSES
DECLARE @db NVARCHAR(50)
DECLARE @spid int
SET @db = 'myDBName'

SELECT @spid = MIN(spid) 
FROM sys.sysprocesses
WHERE dbid = db_id(@db)
WHILE @spid IS NOT NULL
	BEGIN
		SELECT @spid
		EXEC ('KILL ' + @spid)
		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
	END
	
-- DROP DATABASE
DECLARE @cmd1 NVARCHAR(75)
DECLARE @cmd2 NVARCHAR(50)
SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
SET @cmd2 = 'DROP DATABASE ' + @db
EXEC(@cmd1)
EXEC(@cmd2)
	
-- DROP USER ACCOUNT
DECLARE @cmd3 NVARCHAR(65)
IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
EXEC(@cmd3)
GO

Also thinking about the future I combined this sql script with a batch file using SQLCMD and provided it to the onsite support staff so that they can handle the reinitialization for subscriptions that fall outside of their threshold.

@echo off
color 17
Title Device Reinit Clean Up . . .

:: =========================
SET srv=(local)\Instance
:: =========================

SQLCMD -S %srv% -E -i "Device.ReinitCleanUp.sql"
PAUSE

Both files need to reside in the same directory in order to work; however, if you decide to call the sql script from a network share be sure to make the necessary adjustments. Reason being is that we don’t deploy management studio on the subscribers and some of the onsite technical support staff are unfamiliar with SQL Server. So I would rather not take any chances which is why I am going to add an additional condition.

IF HOST_NAME() = 'servername'
BEGIN
	PRINT '**** WARNING ****'
	PRINT 'You attempted to apply the cleanup script against the Production Server!'
	PRINT 'Terminating process!'
END

ELSE
	BEGIN
		-- KILL ACTIVE PROCESSES
		DECLARE @db NVARCHAR(50)
		DECLARE @spid int
		SET @db = 'myDBName'

		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
		WHILE @spid IS NOT NULL
			BEGIN
				SELECT @spid
				EXEC ('KILL ' + @spid)
				SELECT @spid = MIN(spid) 
				FROM sys.sysprocesses
				WHERE dbid = db_id(@db)
			END
			
		-- DROP DATABASE
		DECLARE @cmd1 NVARCHAR(75)
		DECLARE @cmd2 NVARCHAR(50)
		SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
		SET @cmd2 = 'DROP DATABASE ' + @db
		EXEC(@cmd1)
		EXEC(@cmd2)
			
		-- DROP USER ACCOUNT
		DECLARE @cmd3 NVARCHAR(65)
		IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
		SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
		EXEC(@cmd3)
	END