Systems Engineering and RDBMS

Archive for March 24th, 2007

Debugging Stored Procedures in SQL 2000

Posted by decipherinfosys on March 24, 2007

One always has a need to debug one’s stored procedures for functional or performance reasons (and the same applies for user defined functions as well even though debugging functions directly is not supported by the T-SQL Debugger in SQL Server 2000). SQL Server 2000 (Query Analyzer) comes equipped with a T-SQL debugger that allows you to control and monitor the execution of stored procedures. If you do need to debug your UDFs as well then you will need to create a wrapper around them and use a stored procedure. The debugger supports traditional functionality such as setting breakpoints, defining watch expressions, and single-stepping through procedures. The T-SQL debugger window displays the test of the stored procedure to be debugged and provides the debugging options, the code pane displays the SQL statement that you are debugging. Separate output panes are provided for local and global variables for the result of the query and for the call stack. If this is not available on your machine, then re-run the setup and make sure that you select “Development Tools\Debugger Interface” under the “Add components to your existing installation” option.

You debug the procedure by going into the object browser in Query Analyzer, drilling down to the stored procedure, right clicking and clicking debug. Then you can specify the values of the parameters and set breakpoints to debug your code. All the standard options of a debugger i.e. Go, Toggle Breakpoints, Remove All breakpoints, Step Into, Step Over, Step Out, Run to cursor, Restart, Stop debugging, Watch, CallStack and Auto Rollback are supported.

A point worth mentioning is that beginning SP3, Microsoft has changed the debugging functionality. With SP3, the ability to debug stored procedures by using Microsoft Visual Studio 6.0 and older or by using pre-SP3 Query Analyzer is turned off by default. Application debugging (stopping at a T-SQL breakpoint while debugging a client application) is also turned off by default. To enable the debugging functionality, run sp_sdidebug, passing the parameter legacy_on. To disable debugging, pass legacy_off to this procedure. Note that Microsoft doesn’t recommend running the sp_sdidebug stored procedure on production

For limitations of the debugger and troubleshooting issues with the T-SQL debugger, you can take a look at the KB article 280101 at MSDN.

In future blog posts, we will cover how to debug your procedures in SQL Server 2005 and Oracle and DB2 LUW.

Posted in SQL Server | Leave a Comment »