Who2 Article Published

Comments: No Comments
Published on: June 22, 2010

I just had my second article published on June 14, 2010 at SQLServerCentral.  I will be posting a follow-up to that article in the near future.  The follow-up is related to the discussion in the thread for the article.  The article is titled: What, Where, When, How, Who2.  The discussion on it can be found here.  In the discussion there is a note about an error that some people are seeing.  There is also a note about a different proc (who is active) written by Adam Machanic (Blog | Twitter).  In the follow-up I will be looking at the error in specific and comparing what Adam has done to resolve it (since it works in his) – with full attribution 😉 .

Declare Scalar Variable

Tags: ,
Comments: 1 Comment
Published on: June 22, 2010

Lately I have been going through a bunch of maintenance style scripts and fixing them.  Along with fixing them, I have also been updating them to work more efficiently and to work in SQL 2008.  Most have been corrected / updated.  The most recent script I have been looking at is supposed to backup databases and delete old database backups from the fileshare based on parameters passed to the stored procedures from a table.  The backups are working as expected, the deletion of files is not working so well.

This will be just a short entry on some of the frustration involved with getting this script to work properly.  That frustration revolves around the title of the post.

In the Beginning…

The procedure that runs the backups is a modularized script.  The basic workings depend on bit operations / math.  Thus I can store multiple options for the backup job within a single field.  Some of these options are for compression, verify, and to delete the backup files.  Performing the bit math on the values stored in this particular field, I knew that my settings were correct and that various operations were supposed to be happening.  The logic inside the parent procedure was also correct to call the delete module.  Thus I figured the problem had to be inside that module.  So it is in the delete module that we will pick up.

Inside this delete module we have various operations to determine OS version, then loops to get file details, and to move this information from one temp table to another, and then ultimately a delete statement.  The delete statement is run iteratively for each file that matches certain criteria (most notably that the file is older than the retention specified).  Each action is then logged to a Log table.  Here is a snapshot of what the script does to find the file information.

[codesyntax lang=”tsql”]


I didn’t much like this as it was originally written but copied it to a new query window so that I could debug it outside of the proc.  When I copied it, I ended up with something like the following:

[codesyntax lang=”tsql”]


What the Heck¿

I tried running that snippet of code and failed miserably.  Why?  I started receiving an error message and was stumped.  The error was that I must declare scalar variable @SourceDirFOR.  But I have that variable declared.  The size is an appropriate size, I can add a print statement and see that the variable is getting a value assigned to it.  So the problem must be inside the variable and the usage of the FOR Loop there.  In that case, I will just change it up and simplify this process a bit.  So let’s try the following in lieu of the SourceDirFor stuff.

[codesyntax lang=”tsql”]


Alright, I am still getting this error message.  Some research and frustration later, I will have checked the collation settings and checked to see what others have done.  None of it seemed to be matching.  What is going on?  (Mind you this was being done while tired and needing a break.)  Finally I came across something in a forum (By Jeff Moden) that was similar to what I was doing so I checked the code provided there (up to the point of running the xp_dirtree) and ensuring case similarity (though collation was checked and case insensitive collation is being used).  Running that code yielded the exact results that I wanted.  What was the difference?  I compared the xp_dirtree command to mine, the table creation to mine, and the variable declaration to mine.  It was all the same – still getting different results.  This, btw, was being done on the same server in different connections.  The SQL Server version is 2008 sp1, and the OS is 2008 R2.  Finally, I yielded to verifying line by line the differences.  Here is the difference:

[codesyntax lang=”tsql”]



Do you see it?  One little “go” statement was the cause of all of my scalar variable problems.  I don’t know how I ended up with a go statement in my script in that spot, but I overlooked it several times.  That is a really simple fix.  I’m fine with the result though – the script works better now and I prefer the shorter xp_dirtree method.  The moral is that you should batch your statements, but you need to be careful where you place the “;” or “go” statements.

page 1 of 1

June 2010
« May   Jul »

Welcome , today is Thursday, December 12, 2019