Debugging

for RuBoard

The best tool for debugging Transact-SQL stored procedures comes right in the SQL Server box. Query Analyzer now includes a full-featured stored procedure debugger. You can set breakpoints, establish watches , and generally do what debuggers are designed to dodebug code. You can debug procedures from the current version of SQL Server back through SQL Server 6.5 Service Pack 2.

The interface by which this occurs is known as the SQL Server Debug Interface, or SDI for short. It was originally introduced with SQL Server 6.5 and has since been completely integrated with Visual Studio and, now, Query Analyzer.

Setup and Security Issues

You should run SQL Server under a user account, not the LocalSystem account, when debugging. Running under LocalSystem disables breakpoints. If you attempt to debug a procedure and it executes without letting you step through code and without stopping at breakpoints, the SQL Server service is probably set up to log in as LocalSystem.

On a machine with multiple instances of SQL Server, if you do not have a default (unnamed) instance, the debugger won't work properly. Your procedure will simply execute without stopping for breakpoints. It will behave as though SQL Server is running under the LocalSystem account even when it isn't. This is because it erroneously uses the credentials from the default instance for debugging on any instance. The workaround is to create a default instance and set up its login info to match that of the named instance.

If you're debugging under a named instance on a machine that has a default instance, debugging will fail to work if the login info of the default instance does not allow it. Again, this is because SQL Server uses the login info of the default instance regardless of which instance you're actually debugging under. For example, even if you have your named instance set up to use a domain account for the SQL Server service, T-SQL debugging won't work properly if the default instance logs in with LocalSystem. If the account info differs between your named instance and the default instance, the debugger will use the wrong login credentials and may fail to work properly.

General Advice and Caveats

• On Windows NT and 2000, SDI messages are written to the event log under MSDEVSDI.

• You can only debug one stored procedure at a time. If you try to debug another routine while one is currently being debugged , you'll be asked whether to terminate the current debugging session.

• Try not to debug stored procedures on a production server. Debugging can tie up system resources, blocking other users and reducing concurrency.

• Debugging stored procedures over a Windows Terminal Server session does not work properly. The best workaround is to use a different remote control technology such as PC Anywhere, NetMeeting, or Laplink.

• Raising an error within a stored procedure with a severity of 16 or higher while debugging seems to break the debugger. On my server, all debugging options suddenly become unavailable once the message is raised, and the debugger begins to behave erratically.

• Although the debugger's Local Variables window can display sql_variant, cursor, image, text, and ntext variables, you cannot modify them.

• You can't modify a stored procedure while it's being debugged.

• The debugger's Watch window displays only the first 255 characters of a char or varchar variable, regardless of how long it actually is.

• You cannot place a table variable in the debugger's Watch window.

• You can't debug extended stored procedures using Query Analyzer's debugger. Extended stored procedures reside in DLLs and are usually written in C or C++. See Chapter 20 for instructions on how to debug them.

• You can't open a new connection (from within a single instance of Query Analyzer) while debugging. If you need another connection, start another instance of Query Analyzer.

Steps

To debug a procedure, follow these steps:

1. Open the Query Analyzer Object Browser and find the stored procedure you want to debug.

2. Right-click it and select Debug from the pop-up menu.

3. Supply any required parameters for the stored procedure and click OK.

4. The debugger will then start, and you can set breakpoints, add watch variables, and so forth, using the menus or via the keyboard.

5. Note that you can also start a debugging session from the Object Search tool.

Debugging Without a Net

If you're ever forced to debug stored procedures without using the built-in debuggereither at gunpoint or at a remote site where they can't spare enough disk space for Query Analyzerhere are a few tips to help you survive:

• You can lace your code with PRINT statements to send debug info to the results window. PRINT can display any scalar variable and also supports string concatenation and other basic expressions.

• You can use xp_logevent to log diagnostic info in the system error and event logs.

• You can use sp_trace_generate_event to add a user-defined event to a Profiler trace.

• You can use the undocumented sp_user_counter N procedures to set user-defined Perfmon/Sysmon counter values from within Transact-SQL. For example, this would allow you to trace the changes over time in the value of a stored procedure variable in a Perfmon chart or log.

• There are several undocumented trace flags that aid with debugging complex Transact-SQL. Have a look at Chapter 22 for a list of some of them.

• A technique I often use to debug complex stored procedures is to take the code from the procedure and put it in a command batch. Then I can run it a section at a time and comment/uncomment parts of it as I work through the problem.

Debugging Triggers and User-Defined Functions

To debug a trigger or UDF, debug a stored procedure that uses it, even if you have to create one for that express purpose. For example, to debug a trigger, you could first set up a stored procedure that modifies the trigger's table such that the trigger fires. When you debug the procedure, you'll be able to step into the trigger (by stepping into the DML statement). The same is true with UDFs. Set up a stored procedure that calls the function, then debug the stored procedure.

for RuBoard