SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Declare Scalar Variable

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.

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'
-- Start temp table population(s).
CREATE TABLE #_File_Details_01
	( Ident INT IDENTITY(1,1)
	SELECT @Error = @@ERROR
	IF @Error != 0
			INSERT INTO dbo.Process_Log
				VALUES (GETDATE(), @ProcessName, 'ERROR', CONVERT(VARCHAR(15),@Error), 'Create table #_File_Details_01 failed.')
INSERT INTO #_File_Details_01
	EXEC master..xp_cmdshell @SourceDirFOR --, no_output

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:

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'
-- Start temp table population(s).
CREATE TABLE #_File_Details_01
	( Ident INT IDENTITY(1,1)
INSERT INTO #_File_Details_01
	EXEC master..xp_cmdshell @SourceDirFOR --, no_output

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.

DECLARE @BackupServer VARCHAR(128)
		,@BackupShare VARCHAR(128)
		,@DBName		VARCHAR(128)
		,@SourceDir	VARCHAR(512)
		,@SourceFile	VARCHAR(128)
		,@SQL		VARCHAR(1024)
		,@SourceDir2	VARCHAR(512)
SELECT @SourceDir = '\\' + @BackupServer + '\' + @BackupShare + '\' + @@SERVERNAME + '\Databases\' + @DBName + '\'
Select @SourceFile = @DBName + '_*.*'
	( Ident int IDENTITY(1,1)
	,DirFileName varchar(256)
	,Depth Bit
	, IsFile Bit)
 SELECT @SourceDir = @SourceDir+'\'
  WHERE RIGHT(@SourceDir,1)<>'\'
exec master.dbo.xp_DirTree @SourceDir2,1,1

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:

	( Ident INT IDENTITY(1,1)
	,DirFileName VARCHAR(256)
	, IsFile BIT)
-- versus
        RowNum INT IDENTITY(1,1),
        DEPTH  BIT,
        IsFile BIT


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.


No comments.

Leave a Comment

Please register or log in to leave a comment.