Debugging Stored Procedures

Of all the tasks required for building a Web or other type of data-driven application, the process of creating and debugging stored procedures is one of the most crucial—and daunting. The T-SQL Debugger in Visual Studio (VS) 6.0 Enterprise Edition includes a set of debugging features that can help you simplify the process. The T-SQL Debugger, which is an add-in for Visual Basic (VB), is useful for debugging stored procedures that any application might use. With this tool, you can debug stored procedures in SQL Server 2000, 7.0, and 6.5 with Service Pack 2 (SP2). This month, I walk you through installing and using the debugger with SQL Server 6.5 and later. The article assumes that you'll be debugging against a database that's running on the same system as VS. You can access the T-SQL Debugger from within VB to debug your T-SQL stored procedures, no matter which language runs those stored procedures.

Like other Microsoft debuggers, the T-SQL Debugger helps you perform tasks such as controlling breakpoints, inspecting variables, and stepping through your T-SQL code. For example, you can review your T-SQL code line by line and watch as a stored procedure executes each line. This feature is handy when your stored procedure contains IF statements and you must watch the conditional execution. Microsoft implemented the T-SQL Debugger in VS differently from the way it implemented its other debuggers. For example, VB's internal debugger is part of VB. The T-SQL Debugger is an add-in to VB that must work not only with VB but also with SQL Server.

I performed my testing for this article on a Windows 2000 Professional system that included SQL Server 2000, SQL Server 7.0, and VS 6.0 Enterprise Edition.

Using T-SQL Debugger

To enable the debugger, start VB, open the Add-In Manager from the Add-Ins menu, then find the T-SQL Debugger. Select the Loaded and Load on Startup options; click OK. After you've enabled the T-SQL Debugger, set its options before you use it. You can display the options by selecting Tools, T-SQL Debugging Options from the menu bar. Figure 1 shows the default settings.

The first setting, Automatically step into Stored Procedures through RDO and ADO connections, controls how VB steps into your stored procedures from the VB debugger. If this option is selected, VB will go right from your VB code into the stored procedure debugger when it reaches the line executing the stored procedure. If you don't select this option, the stored procedure debugger will never drop automatically into stored procedures during debugging. The second setting, Use Safe Mode (transaction rollback) for Stored Procedure calls, controls the debugger's Safe Mode. When you use Safe Mode, any database changes you make during the stored procedure debugging sessions automatically roll back. You can use this dialog box to set the option on or off during a debug session. The third option, Limit SQL Output to the following number of lines per resultset, controls the number of information rows a stored procedure returns during debugging. This option lets you set a low number when you're initially debugging a stored procedure that returns many rows. The last option, Login Timeout value for retrieving stored procedure text, controls the timeout for retrieving the stored procedure text during the debugging process. You usually don't need to change this option. After you've set the options, click OK.

Debugging the Stored Procedures

To use the T-SQL Debugger, select Add-Ins from the Add-Ins menu in VB, then select T-SQL Debugger to display the debug settings window, which Web Figure 1 shows. You can also start the debugger by right-clicking a stored procedure in the Data Environment and selecting Debug. On the Settings tab, select (or create) a Data Source Name (DSN) that points to the database containing the stored procedures you're going to debug. You can also supply the server, database, and user information without using a DSN in this dialog box. To control the stored procedure operation, set the Lock Type, Result Set, and Options at the bottom of the dialog box.

After you've selected the database and configured the options to use with it, click the Stored Procedure tab. When you click this tab, the T-SQL Debugger retrieves the stored procedures and their parameters from SQL Server. Select the stored procedure you need to debug, set its parameters, then click Execute. To set a parameter to a stored procedure, select the parameter in the Parameters list and enter its value in the Value box.

When you click Execute, the T-SQL Debugger interface opens with the stored procedure code visible. At this stage, the stored procedure isn't executing yet; it's merely open in the top window and ready to go. To run the stored procedure, press F5 or click the first icon on the toolbar. You can also use the Go menu option on the Debug menu. This action launches the stored procedure, and if you haven't set any breakpoints, the debugger displays the stored procedure's results in the Output window at the bottom of the T-SQL Debugger interface. You can set a breakpoint before you execute the stored procedure by clicking once in the line where you want to stop and pressing F9 or clicking the Toggle Breakpoint icon on the toolbar. After you've set the breakpoint, you can execute the stored procedure again by pressing F5, and the procedure stops executing when it reaches the breakpoint.

At each breakpoint, any variables or parameters in the procedure show up in either the Local Variables or Global Variables window. You can continue the execution from a breakpoint by clicking Step Into, Step Over, or Run to Cursor on the toolbar. You can also execute Step Into by pressing F8. Figure 2 illustrates a typical debugging session with a simple stored procedure in break mode. The highlighted box around the Select statement (the next line to execute) shows that the stored procedure is in break mode, and the red dot in the left margin flags the breakpoint that has been set on that line. Figure 2 also shows the value of the @Au_Id parameter in the Local Variables window.

After a stored procedure has finished executing in a debugging session, you must select Restart (Shift+F5) from the Debug menu to restart the debugging process. Then, you must press F5 to restart the execution of the stored procedure.

Minor Bugs in the Debugger

Despite the advantages the T-SQL Debugger offers, it is a complex set of software components and suffers from a few minor problems. For example, when I launched VB and tried to debug a stored procedure in SQL Server 2000, I received error message 17750, which the Application Event log defined as Cannot load the DLL SDI, or one of the DLLs it references. Reason: 126 (The specified module could not be found).

After searching unsuccessfully for the unfamiliar sdi.dll in Microsoft's Knowledge Base, I checked the release notes for VS 6.0 SP4 and found a report that the T-SQL Debugger won't install correctly on systems with SQL Server 2000. (It will install correctly on systems with SQL Server 7.0 and 6.5 SP2.) Following the workaround the note provided, I copied and registered the first three files (mssdi98.dll, sdi.dll, and sdi60.dll) from the sqdbg_ssMSSQLbinn folder on VS disk 2, transferred the files to the MSSQLbinn folder and successfully launched the debugger.

I also noticed that after several debugging sessions, T-SQL Debugger's sensitivity kicked in, and I began to receive ODBC-related error messages from it, such as Communication Link Failure. As soon as I saw one of these messages, I shut down VB and restarted it. Usually I was able to conduct several debugging sessions before the errors recurred—after about 10 to 20 minutes on my system.

In addition, I have found that when I change a variable's value in the Local Variables window and execute another line after the change, the variable value usually reverts to its original number. Only occasionally can I continue execution with the new value, as the documentation states. And sometimes the T-SQL Debugger locks up, requiring you to use Task Manager to close VB.

More Information About T-SQL Debugger

Using the T-SQL Debugger is simple, but getting it to run on SQL Server 2000 can be complicated. Unless otherwise noted, the steps I've demonstrated in this article work when the T-SQL Debugger is running on the same system as SQL Server 6.5 SP2 and later. For more information about installing, configuring, and using the T-SQL Debugger, see the sidebar "Further Reading." For more information about debugging, see "Debugging with Visual InterDev," August 2000, and "Debugging by Design."