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.

Code block

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)
, OUTPUT VARCHAR(512) )
 
SELECT @Error = @@ERROR
IF @Error != 0
BEGIN
INSERT INTO dbo.Process_Log
VALUES (GETDATE(), @ProcessName, 'ERROR', CONVERT(VARCHAR(15),@Error), 'Create table #_File_Details_01 failed.')
END
 
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:

Code block

DECLARE @SourceDirFOR VARCHAR(255)
 
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)
, OUTPUT VARCHAR(512) )
go
 
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.

Code block

DECLARE @BackupServer VARCHAR(128)
,@BackupShare VARCHAR(128)
,@DBNameVARCHAR(128)
,@SourceDirVARCHAR(512)
,@SourceFileVARCHAR(128)
,@SQLVARCHAR(1024)
,@SourceDir2VARCHAR(512)
 
SELECT @SourceDir = '\\' + @BackupServer + '\' + @BackupShare + '\' + @@SERVERNAME + '\Databases\' + @DBName + '\'
Select @SourceFile = @DBName + '_*.*'
 
CREATE TABLE #DirTree
( Ident int IDENTITY(1,1)
,DirFileName varchar(256)
,Depth Bit
, IsFile Bit)
go
 
 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:

Code block

CREATE TABLE #DirTree
( Ident INT IDENTITY(1,1)
,DirFileName VARCHAR(256)
,DEPTH BIT
, IsFile BIT)
go
 
-- versus
 
 CREATE TABLE #DirTree
(
        RowNum INT IDENTITY(1,1),
        Name   VARCHAR(256) PRIMARY KEY CLUSTERED,
        DEPTH  BIT,
        IsFile BIT
)

Conclusion

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.