Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
The Transact-SQL Debugger, Part 2
Last updated Mar 28, 2003.
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:
- It shows me where I am in the code
- It lets me “step” through the code, which means to move one line at a time
- 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:
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:
Let me number the parts of the screen, so I can talk about what they are and what they do:
Up in the top right-hand side is the icon bar (marked number “1”). The 5th 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.
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).
Now I’ll highlight the same code from before and show you the result in the Breakpoints panel.
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.