The SQL Debugger
One of the most significant features in Query Analyzer is the SQL Debugger. With the various commands and programming constructs in Transact-SQL (T-SQL), stored procedures can perform some pretty complex operations. You might have as much programming logic built into stored procedures as in some of your other application routines written in other programming languages. As you develop and test your application code written in Visual C++ or Visual Basic (or whatever programming language you might be using), it is likely that you will use a debugger to step through the code and verify that it is working correctly or to identify where the logic is breaking. When your stored procedures are not executing correctly or they are returning the wrong result, you need to debug them as well.
In the old days, the only way to debug stored procedures and step through them line by line was to rewrite them as SQL batches and execute the statements individually or in small groups. The problem with this approach was that the contents of local variables didn't carry over between batches, so you had to redeclare and reinitialize the variables for each batch and set the values to what they were at the end of the previous batch. This process was tedious, and the SQL statements would optimize differently when passed a variable rather than what would have been a stored procedure parameter. For more information on how queries are optimized, see Chapter 35, "Understanding Query Optimization."
The only other way to track what a stored procedure was doing was to litter it with print statements to display the status of the procedure and the contents of local variables and parameters at various points. I remember using this approach one time to debug what looked like a simple problem. I had a stored procedure that needed to delete a parent record, and then all related child records. Everything worked just fine by using local variables and separating each SQL statement. My print/debug statements didn't shed any light on the matter, either.
After spending about half a day trying to track it down, I finally identified the problem?I was missing a parameter to the stored procedure that was being called within the main procedure, shifting the parameter values over one and causing the wrong value to be used to find the matching rows. (I could have avoided this by passing the parameters by name rather than position. Live and learn!) If only I had had a debugger available to debug the stored procedure, I could have identified the problem in a minute instead of a day.
The First T-SQL Debugger
Stored procedure debugging was available for SQL Server prior to SQL Server 2000, but it wasn't part of the SQL Server installation. Since the release of Visual Studio 6, Visual Interdev has included a T-SQL Debugger. Prior to this, it was available inside Visual Basic Enterprise 5.0 and Visual C++ Enterprise 4.2.
However, getting the debugger up and running was an arduous process. You had to have all the following pieces in place:
In addition, for SQL Server versions prior to SQL Server 2000, the debugging components were not installed on the server by default. To get the debugger to work, these pieces had to be installed manually. This caused a lot of headaches: Programmers who wanted to use stored procedure debugging had to bother the administrator, who might or might not have seen the need to install these pieces. It's a rare administrator who will upset the delicate balance of his smoothly running SQL Server to indulge the whim of a developer, unless some sort of bribe is involved.
You also needed to ensure Distributed COM (DCOM) was installed properly on the server and the client. You had to make sure the DCOM configuration on a Windows 98/Me machine supported debugging.
The main problem with using this debugger was that you had to install Visual Interdev. This was a pretty large, involved install if all you wanted to do was use a SQL Debugger. Also, the debugger was not part of a standard query tool like Query Analyzer, nor was it a standalone tool. It had to be brought up via a Data View window in Visual Studio.
People needed a T-SQL Debugger that could be installed along with the SQL Server Client Tools that was integrated with Query Analyzer. That finally came along in SQL Server 2000.
Using the T-SQL Debugger in Query Analyzer
Query Analyzer that ships with SQL Server 2000 now includes an integrated T-SQL Debugger. This is a full-featured debugger that lets you step through stored procedure code a line at a time, set breakpoints, view the contents of local and global variables, and see the results of the T-SQL statements as they execute.
The necessary steps for invoking the debugger are not easily apparent. To debug a stored procedure from within Query Analyzer, follow these steps:
At this stage, a couple of common problems could prevent full use of the debugger. These problems are discussed in the next section.
Debugging a Stored Procedure
If everything is properly configured when you invoke the SQL Debugger, you will first be presented with a dialog where you can enter values for the procedure parameter(s), if any (see Figure 6.15). Click the Set to Null check box if you want to specify a NULL value for a parameter. There is also a check box that lets you specify whether you want the procedure to automatically roll back or not. Auto Rollback is the default option. This feature allows you to test and debug a stored procedure that modifies data without the data changes being permanent, so you can repeatedly debug the procedure with the same datasets each time. The debugger initiates a BEGIN TRAN statement before executing the procedure, and automatically issues a ROLLBACK TRAN when debugging is stopped. If you want to commit the changes made to data while debugging the stored procedure, uncheck the Auto Rollback option.
Figure 6.15. Providing input parameter values to the SQL Debugger in Query Analyzer.
Once you have provided any necessary input parameters and clicked the Execute button, you will next see the SQL-Debugger interface with a yellow arrow pointing to the first line in the stored procedure (see Figure 6.16). At this point, you can step through the stored procedure one line at a time, or you can set breakpoints and run to the breakpoints. All operations in the SQL Debugger are controlled by selecting the buttons on the Debugger toolbar, by right-clicking in the debugger window to bring up the command menu, or by using the keyboard shortcuts for the Debugger commands. Table 6.1 describes the available commands in the Debugger and the corresponding keyboard shortcuts.
Figure 6.16. The SQL Debugger interface.
The Debugger Window Panes
The SQL Debugger window consists of five window panes. The top pane is the Source Code window pane that displays the text of the procedure, user-defined function, or trigger that is currently being debugged. This window pane indicates the current execution point and is where you can set and display breakpoints.
The three middle window panes are the Local Variables window pane, the Global Functions window pane, and the Callstack window pane. The Local Variables pane displays the name, value, and type of each input and output parameter and any local variables defined within the current procedure scope. You cannot add or remove any of the variables listed in this pane, but you can change the values assigned to the variables. This is useful for evaluating and testing different variable values and their impact on program flow and execution.
The Global Functions pane displays the values currently returned by the Global Functions based upon the current state of execution. You cannot modify the values for the global functions, but you can add additional global functions to the list to monitor different values, such as @@ROWCOUNT, @@IDENTITY, @@ERROR, or @@NESTLEVEL. For more information on the available global functions, see Chapter 26, "Using Transact-SQL in SQL Server 2000."
The Callstack window pane shows the list of currently open procedure calls, with the top procedure being the currently active procedure. (The currently active procedure is the one that determines the scope of the values displayed in the Local Variable and Global Function window panes.) You can change the values displayed in these panes and the Source Code window pane by clicking on the other procedures listed below the top procedure in the Callstack pane. The Callstack pane is useful to keep track of the nesting levels of the currently executing procedures.
The bottom window pane is the Results Text window pane. This window pane displays any results generated by the stored procedure being debugged as well as any print or error messages generated.
The Status Bar
The status bar at the bottom of the main Debugger window displays the current state of execution (Running, Completed, or Aborted), the name of the SQL Server the debugger is currently connected to and the login ID used to connect, the current database context, and the line number and column where the cursor is currently positioned within the Source Code window.
Debugging Triggers and User Functions
Triggers are fired only when a data modification statement runs against the table on which the trigger is defined. It isn't possible to directly invoke or debug a trigger, but you can debug triggers and user-defined functions if they are invoked from within a stored procedure you are debugging. To step into and debug a function or trigger, use the Step Into debugger option when invoking a command that includes a function or performs a modification on a table for which a trigger has been defined. Once the debugger is in the user-defined function or trigger, you can debug it just like debugging stored procedures, including setting break points, stepping into other user-defined functions or stored procedures, or aborting or restarting the debugging session.
If no stored procedure exists that invokes the user-defined function or trigger you want to debug, you will need to write a simple test procedure that executes a SQL statement you can step into that invokes the user-defined function or trigger you wish to debug.
Limits of the Debugger
The debugger provides a pleasing interface for monitoring local procedure variables and global variables (@@trancount, for example) at each stage of execution. However, the debugger has the following limits:
Common Debugger Problems
The most common problem with the debugger is attempting to run the server under the local System account. If you are running the server locally on your development box, and don't expect to need to interact with network services, it is fairly common (although not recommended) to configure a server to use the local system account.
Regardless of whether the server is running locally, if the server is using the local system account and you are running a Windows 2000 or Windows NT client, you will see the following message box when you run the debugger:
If you continue, the debugger will load, but you will not be able to set breakpoints or step through the procedure. If you look in the system application log using Event Viewer, you will see this error message logged, from source SQLDebugging98:
To resolve this, change the logon account. In an enterprise setting, it is most appropriate to use a system account that restricts interactive logins, and that has been set up specifically for use by the SQL Server service. You will want to do this to take advantage of replication and multiserver administration features in any event, so take this chance to set up a separate domain account.
It is also possible to use the local Administrator account to get the debugger working. If you normally log on to your machine with an account that is different from the one under which the SQL Server service runs, you will also need to follow the instructions in the "DCOM Configuration" section later in this chapter.
Changing the Server's Logon Account
From Windows 2000, on the machine running SQL Server, follow these steps to change the logon account to local Administrator:
Reconnect to the server from Query Analyzer. You should be able to use all features of the debugger.
Under Windows 2000, you can examine the distributed COM configuration by running DCOMCNFG.EXE. If you plan to debug stored procedures using logon accounts different from the one used by SQL Server, you will need to follow these directions on the SQL Server computer:
Windows 98/Me might have DCOMCNFG.EXE in the \windows\system directory, but some installations do not. The most reliable way to enable DCOM on these systems is to follow these steps: