Debugging Stored Procedures and Triggers

By now, it should be obvious that stored procedures and triggers can represent a significant portion of your application's code. Even so, for a long time, no decent debugger support existed for stored procedures. (The typical debugging tool was the liberal use of PRINT statements.) Some ISVs jumped in and helped by adding some "pseudo-debugging" products ”but these products didn't have access to the actual SQL Server execution environment. They typically added debugging support by doing tricks behind the scenes, such as adding additional PRINT statements or adding SELECT statements to get the current values of variables . Although some of these products were helpful, they had significant limitations: typically, they couldn't step into nested stored procedures or into triggers. A Transact-SQL debugger was always prominent on the wish list of SQL Server customers. Anybody who has done any serious coding in Transact-SQL will know firsthand how frustrating it is not to have a proper debugger and probably has spent many late nights putting PRINT statements into stored procedures.

Still, it didn't make sense for the SQL Server developers to write a new SQL Server_specific debugger because there were already too many debuggers on the market. If you're like most programmers, you want to do your work in one development environment. For example, if you are a C programmer, you probably want to use the same debugger on your SQL code that you use on your C code. Or if you program in Visual Basic, you probably will want to use the Visual Basic development environment for debugging. Fortunately, this environment-specific debugging capability now exists and its availability is rapidly expanding. If you are a developer in Microsoft Visual C++, Microsoft Visual J++, or Visual Basic, you can now debug Transact-SQL using the same debugger you use in those environments.

To accomplish this, the SQL Server developers defined a DLL and a set of callbacks that SQL Server would load and call at the beginning of each SQL statement. In essence, they defined a set of debug events that would allow them to control the execution on a statement-by-statement basis within SQL Server. This has come to be known as the SQL Server Debug Interface, or SDI. The interface that originally shipped with the version 6.5 release was a work in progress. SDI's first customer was Visual C++ version 4.2. SQL Server 6.5 shipped several months before Visual C++ 4.2, and, of course, the developers didn't get the interface quite right. Specs are never perfect, and they nearly always get tweaked ”at a minimum ”during implementation. This was certainly true for SDI, so to use it with Visual C++ version 4.2 or later you need SQL Server version 6.5 with Service Pack 1 or later. The developers debugged the debugging interface using the Visual C++ team as guinea pigs, and now other development tools are adding support for SDI. Today, SDI is available with Visual C++, Visual Basic, Visual J++, and other development tools from Microsoft. (The exact packaging is always subject to change, but in general, the debugger support for Transact-SQL is available only in each product's Enterprise edition.)

Although the interface is quite likely to change in future releases, it is also made available via a technical note to ISVs that want to add SQL Server debugger support. SDI is a specialized interface of interest only to those writing debuggers, so it is not considered a general feature of SQL Server.

With the existence of SDI, you now have a real debugging environment for Transact-SQL if you're using Microsoft Visual Studio. For example, as a C/C++ developer using Visual Studio, you can:

• Perform line-by-line debugging of all your Transact-SQL code.
• Step directly from your C code executing on your client machine into the Transact-SQL code executing remotely at the SQL Server machine.
• Remotely debug your procedures, with the actual execution of the procedures happening at the SQL Server machine and your debugging environment happening locally. (Or if you prefer, you can do it all from one machine.)
• Set breakpoints anywhere in your SQL code.
• Watch the contents of SQL Server local variables and certain system functions. You can even examine function values that are not used in your SQL code: for example, you can watch current status codes using @@ERROR or the number of rows selected using @@ROWCOUNT.
• Modify the values of most variables in a watch window, testing conditional logic in your code more easily. (Note that variables with datatypes for which there is no direct mapping in C cannot be edited in a watch window.)
• Examine the values of parameters passed to stored procedures.
• Step into or over nested procedures. And if a statement causes a trigger to fire, you can even step into the trigger.
• Use Visual Studio to edit your procedures and save them to the server. This makes it easy to fix bugs on the fly. SQL keywords and comments in your code are color coded, as they would be in C, to make them easier to spot.
• Optionally send results of your SQL statements to the result window directly in Visual Studio.

The SDI is implemented via the pseudo_extended stored procedure sp_sdidebug . (The sp_ convention was used so that the procedure could be called from any database without being fully qualified.) By "pseudo," we mean that, like a normal extended stored procedure, you will see an entry in the sysobjects table of type X for sp_sdidebug . But unlike a normal extended stored procedure, the code for sp_sdidebug is internal to SQL Server and does not reside in a separate DLL. This is true for a few other procedures as well, such as the remote cursor calls made by ODBC and DB-Library cursor functions. This was done so that new capabilities could be added to the server without having to change the tabular data stream (TDS) protocol that describes result sets back to the client application. It also eliminates the need for new keywords ( potentially breaking a few applications) when the commands are of the sort that would not be executed directly by an application anyway.

You should never call sp_sdidebug directly. The procedure exists to load a DLL that the provider of the debugger would write and to toggle debugging on and off for the specific SQL Server connection being debugged. When debugging is enabled, the DLL is given access to internal state information for the SQL Server connection being debugged. All of the APIs defined in the interface are synchronous calls, and they are called in the context of the thread associated with the connection, which allows for callbacks to SQL Server to occur in the context of the client's thread. The internal Process Status Structure (PSS) in SQL Server holds status information for the connection being debugged, and the DLL is then able to read this structure to determine local variable, parameter, system function, and symbol information.

The debugging support of the Enterprise editions of the programming tools seems pretty normal if you are already familiar with the environment. Typically, the biggest problem people have with debugging Transact-SQL is getting it configured in the first place. Here are some tips that might help to you in debugging Transact-SQL from Visual Studio:

• You must use the Enterprise edition, not the Professional or Standard edition.
• Run SQL Server under a user account, not as Local System. You can change this in the Services applet of the Windows NT Control Panel. If SQL Server is running under the local account, break-points are ignored. While you're running the Services applet, also make sure that the RPC Service and the RPC Locator Service are running. You might consider setting them to start automatically. Make sure that the user account that is doing the debugging has access to the sp_sdidebug stored procedure. By default, only a system administrator has permission to run this procedure.
• SQL Server Debugging must be enabled in each of the Visual Studio components from which you want to allow debugging. Check the documentation for the particular programming environment for details. For example, in Visual Basic 6 (Enterprise edition) select Add Ins and then Add In Manager. Toward the bottom, there will be a selection for the VB tsql debugger; select it and then click the Loaded/Unloaded box. This will add the debugger to the Add In list. Do not debug on a production server. Due to the added overhead and break-in nature of the debugging product, you could adversely affect other users.

For simple debugging, you might find yourself still using the PRINT statement; for tougher problems, you might come to regard the new debugging capability as a lifesaver. Ron Soukup demonstrated this capability at the SQL Server Professional Developer Conference in September 1996, and this five-minute sidebar of his two- hour presentation seemed to generate more interest than anything else he covered!

Execute(" any string ")

The ability to formulate and then execute a string dynamically in SQL Server is a subtle but powerful capability. Using this capability, you can avoid additional round-trips to the client application by formulating a new statement to be exe-cuted directly on the server. You can pass a string directly, or you can pass a local variable of type char or varchar. This capability is especially useful if you need to pass an object name to a procedure or if you want to build an SQL statement from the result of another SQL statement. For example, suppose that we had partitioned our database to have multiple tables similar to the authors table. We could write a procedure like the one shown below to pass the name of the table we want to insert into. The procedure would then formulate the INSERT statement by concatenating strings, and then it would execute the string it formulated:

 CREATE PROC add_author @au_id char(11), @au_lname varchar(20), @au_fname varchar(20), @tabname varchar(30) AS BEGIN DECLARE @insert_stmt varchar(255) SELECT @insert_stmt="INSERT " + @tabname + " (au_id,     au_lname, au_fname, contract) VALUES ('" + @au_id +     "','" + @au_lname + "','" + @au_fname + "', 1)" EXECUTE (@insert_stmt) END EXEC add_author '999-99-1234', 'Pike', 'Neil', 'authors'