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.

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.

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”]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE TABLE#DirTree

(Ident intIDENTITY(1,1)

,DirFileName varchar(256)

,Depth Bit

,IsFile Bit)

go

--versus

CREATE TABLE#DirTree

(

RowNum INTIDENTITY(1,1),

Name VARCHAR(256)PRIMARY KEY CLUSTERED,

Depth BIT,

IsFile BIT

)

[/codesyntax]

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.

Within the world of SQL Server there are a few things one can be certain of – things will change. This is true of the features in SQL Server. Additionally, Extended Events is constantly evolving which underscores this constant change.

What is this gaping hole in the coverage of Extended Events? To be honest, it is not a very complicated topic or very difficult gap to fill. It’s just something that has been overlooked. The gap boils down to this: how does one consistently find the correct path to the Extended Event Log file (XEL file)?

Legislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.