SQL Server Reference Guide

When you’re working with Transact-SQL (T-SQL) code, you’re going to make mistakes. I know this might surprise some folks, but it’s just going to happen. It’s inevitable. There’s no way around that.

Now that you know my position on whether you’ll make a mistake or not, I’ll explain a new tool you have to help catch those mistakes before they make it into production, or at least find them a little faster when you do hit an issue.

The tool is called the Transact-SQL Debugger (which I’ll just call Debugger from now on), and is available on, for and with SQL Server 2008 and higher. That means it doesn’t work against SQL Server 2005 and lower, although I do have a way of doing that. I’ll tell you how I do that at the end. I’ve already written about this tool in a previous tutorial, but in this tutorial I’ll show a more practical example and explain the screens a little further.

As I mentioned in the earlier article, a “Debugger” is a tool that lets you analyze code. Beyond that, you have to talk about exactly what the Debugger in question is set up to do. For the one in SQL Server (2008 and higher, remember) I use it for three purposes:

1. It shows me where I am in the code
2. It lets me “step” through the code, which means to move one line at a time
3. It lets me look at the values of my variables as the code runs

It’s not perfect, and it’s certainly not for everyone. For more robust debugging or really complex and lengthy code, I turn to the incredibly long-named Visual Studio Team Edition for Database Professionals, or just “Data Dude” to those in the know.

There are two parts to the Debugger — one sits on the server and the other is the T-SQL Debugger feature in SQL Server Management Studio (version 2008 and higher). Working together, it runs a line of code at a time, jumping through the conditional logic (like into a stored procedure and back) as you “step” through the code. Because of that interaction, there are a couple of things you need to do, and because you need to do these things, I’ll make this very plain:

Only Run the Transact-SQL Debugger (Client and Server) on a Test System.

I can’t emphasize that enough. It’s just a terrible idea to debug code on a live server, with really VERY few exceptions, like battlefield conditions or something akin to it.

Even on a local system where you are going to run the debugger, you need a couple of things in place. First, you’ll need some firewall exceptions– port 135 on both, and you need to find and add the programs called ssms.exe (on the client) and Sqlserver.exe (on the server). If you are using IPSec (you’ll know if you are) then you also need UDP ports 4500 and 500 excepted. All of this information is located in Books Online for how to do that – if you’re using later versions of SQL Server you’ll get a popup the first time you use it that will do all of this for you if you’re on the same machine:

Figure 1

There are also really high security rights needed for this tool, for very good reason. In fact, you need to have sysadmin rights on the server. This is fine, once again, for a testing or development server, but every developer should not have rights to your production server.

With all of that stated, I’ll return to my previous example in the earlier tutorial, but add a few things to make it more interesting. I was working this morning on some code that runs from a middle tier system. These T-SQL statements (called “Dynamic SQL” because it is not a stored procedure or function) which in turn called a stored procedure. The reason isn’t important, but it does illustrate a great use for the Debugger. Because this represents some “branching” (code going off to other code and then returning to the original code) and it involves variables, I’ve created some more sample code so that you can safely use to run on your test system. I’m using the AdventureWorks sample database, which I’ve written about here.

Here’s the code in its entirety – I’ll break it down for you next:

/* T-SQL Debugger */
USE AdventureWorks;
GO

CREATE PROCEDURE usp_TestDebuggerRemoveWhenFinished @ProductID int
AS 
SELECT [Name], [ProductNumber]
FROM AdventureWorks.Production.Product
WHERE ProductID = @ProductID
GO

DECLARE @a int = 1
DECLARE @b int = 0

WHILE @a < 5
BEGIN
	EXEC usp_TestDebuggerRemoveWhenFinished @a
	SELECT @a = @a +1
	SELECT @b = @b + 1
END
GO
DROP PROCEDURE usp_TestDebuggerRemoveWhenFinished;
GO

In the first block (indicated before the first “GO”) I’m just setting the context of AdventureWorks. The second block (up to the next “GO”) is where I create a simple stored procedure. It selects rows of data from the Products table based on a product identification number. It’s a trivial example, but will suit our purposes here.

The next block (up to the next “GO”) is what I will end up showing you for the Debugging session. This example simply iterates two values and uses one of them in a loop to call a stored procedure with that value. I took this from my last article.

If you’re following along, you can highlight that code:

DECLARE @a int = 1
DECLARE @b int = 0

WHILE @a < 5
BEGIN
	EXEC usp_TestDebuggerRemoveWhenFinished @a
	SELECT @a = @a +1
	SELECT @b = @b + 1
END
GO

And then either press ALT-F5, click on the green arrow icon in the toolbar, or click the menu item called “Debug” and then click “Start Debugging." In any case, your screen will end up looking something like this:

Figure 2

Let me number the parts of the screen, so I can talk about what they are and what they do:

Figure 3

Up in the top right-hand side is the icon bar (marked number “1”). The 5^th icon in (looks like a paragraph with a line going into it) is the part that runs the line where the area marked number “2” has the yellow arrow pointing. You can get the same effect by pressing F11. The icon just to the right of this one (looks like a paragraph with a line going all the way around it) skips over that line to the next one, and of course the icon to the right of that moves to the previous line.

Just to the right of that icon is a screen icon with a red “gumball” in it. This is for “Breakpoints” — meaning a place in the code where you’ve put a symbol. It’s a way for you to either stop the flow of code or go directly to it. I’ll show you how to set one of those up later.

The main area (marked number “3”) is showing your code.

The area to the bottom and left (marked number “4”) has two tabs: One for the variables involved in the code that is currently running, and another for variables you have set to “watch." I’ll show you the “watch” variables in a moment, but I want to explain a little more about the “locals” part. The variables that you see there in my screen are @a and @b. Those are not found in the stored procedure — but the values that @a contains will be passed to the procedure once the line gets there. Notice that I have no numbers in the variables — the system know about them, but they haven’t been assigned. When I get to the part with the stored procedure, this window will change to the variables in that area — hence the name “locals.”

The area on the bottom right (marked as number “5”) has four tabs in it. The first is the “Call Stack," which tells you which line and which area you’re running in. That is a vital piece of information for teasing out “Where am I” in the code.

The next tab is the “Breakpoints." These are places you set in code that you want to mark as a place you want to watch. The next is the “Command Window” — a place where you can automate SQL Server Management Studio. I actually don’t use this tab very often.

The final tab here is the “Output” area. This shows you the underpinnings of the system as the code runs. Although I’ve looked here in interest, it really doesn’t meet the three goals I have for the product.

With all of that said, I’ll press F11 to start walking — or “stepping” — through the code. I’ve pressed F11 twice, and so now the pointer has moved (to line 23 in my case) and the variables now have a value. You can also see that the Output panel shows that the client (SSMS) has been able to connect to the server (and Instance named UNIVAC) correctly. Now I press F11 a few times and you can see my window change dramatically.

Figure 4

The main editor has jumped to the line of code that will run next — which is in the Stored Procedure I called in the Dynamic SQL code. Notice also that the Locals tab has changed to show the value of the variable I passed to this procedure. And also, the Call Stack tab shows that I’ve moved into another block of code.

And in point of fact, this is where I found the issue I was debugging this morning. I got a call that the code was returning incorrect data. Well, that’s always a cause for alarm, so I ran the code on my development server. The results were correct. This of course points to either different data or different code on the production system. I copied the code over, and ran the debugger to walk through the flow. And when this procedure came up, it was completely different than development! It seems that when a developer moved some of her code this morning, she grabbed an old version of this stored procedure, so when the old code called the new procedure, well, there you have it.

I’ll go ahead and click the blue square icon (the Break command) to leave the debugger, and I’ll return to the code to set a Breakpoint, just so that you can see it. It’s actually quite simple — you highlight a line of code, and then click Debug in the menu and then click “Toggle Breakpoint," or move your cursor to a line and press F9, or simply double-click in the gray space next to the line (26, in my case).

Figure 5

Now I’ll highlight the same code from before and show you the result in the Breakpoints panel.

Figure 6

You can see that line 26 in my script file has the breakpoint identified. Oh, while we’re in here, you’ll notice on the left I’ve set a “Watch.” All I had to do to make that happen was to right-click a variable back on the “Locals” tab and select “Add Watch.” Why is that interesting? Well, recall that the Locals tab shows only the variables in effect at the time – they changed when the code got to the Stored Procedure. What If I still want to see the variables from the Dynamic SQL as I go? Just set a watch, and they show up here.

OK — I mentioned early in this tutorial that I would tell you how I use the Debugger against SQL Server 2005 and lower. Truth is, you can’t hit SQL Server 2005 servers with the debugger no matter what you do, since there is a server piece that needs to be listening, and that just isn’t there.

So here’s what I do: I bring the code over to my development system, restore as much of the database as will fit (sometimes just schema and sample data) and run the code there. As long as I don’t include any SQL Server 2008-specific code as a fix, there’s no problem. After all, regardless of the source, I always debug on a test system anyway.

InformIT Articles and Sample Chapters

Although this is an older reference, Using Visual Studio .NET to Debug Applications, from Sams Teach Yourself .NET in 21 Days, has much in common with the way the debugger was implemented for SQL Server.

Books and eBooks

You’ll find a lot more information about debugging in this reference on .NET, Advanced .NET Debugging.

Online Resources

Microsoft’s Information on the Debugger is here.