Debugging Stored Procedures in Visual Studio .NET
Visual Studio .NET was designed to support integrated debugging for SQL Server. Unfortunately, there are so many limitations on what a developer should and shouldn't do and the process for configuring debugging is convoluted enough that it is difficult to decipher how well this technology works. At this juncture I can tell you the benefit you are supposed to be able to derive, and I can also tell you I have not reliably and consistently been able to get integrated SQL debugging to work.
Visual Studio .NET was designed to allow you to debug SQL Server stored procedures in the Visual Studio .NET IDE using the same debugger you use to debug VB .NET code. The benefit here is powerful, and in Chapter 17 we will talk about integrated debugging tools, breakpoints, watches, and other benefits of integrated debugging. However, there are a lot of caveats, and the process for debugging is not clearly documented.
The first and perhaps most obvious caveat is that you should not debug a production database. The recommended approach is to have the SQL Server database start up with a user account rather than a system account. Additionally, debugging may lock rows and tables, making it impossible for other users to use the database while you are debugging it. This clearly suggests that SQL Server integrated debugging should be done on a duplicate, nonproduction database. The second stumbling block is that there are too many configuration steps and prerequirements, making it very difficult to configure stored procedure debugging. The writers of the help documentation need to refine the instructions and make the process much simpler.
A recommended process is to debug stored procedures with a local copy of the database running in MSDE on your workstation. This seems likely to be the least disruptive approach to other developers and users, but even the preconditions for configuring MSDE stored procedure debugging seem a bit unclear. In the next two subsections, I provide an overview of the steps to configure debugging for MSDE and SQL Server, but as I mentioned, this process needs refinement.
Configuring the Microsoft Data Engine for Stored Procedure Debugging
The steps for debugging MSDE described in this section are based on the Visual Studio .NET help topic “Enabling SQL Debugging on SQL 2000 Desktop Engines.” You can view this material by inserting the following link in the URL address bar in Visual Studio .NET: ms-help://MS.VSCC/MS.MSDNVS/vsdebug/html/vxlrfsettingupsqldebuggingenablingsqldebuggingonsql2000desktop.htm.
1. | Copy MSSDI98.DLL to the SQL Server inn directory. |
2. | If Visual Studio .NET is not installed on the same workstation containing MSDE, complete steps 3, 4, and 5. |
3. | Copy SQLDBG.DLL to program filescommon filesmicrosoft sharedsql debugging. If Visual Studio .Net has been installed then SQLDBG.DLL should already be there. |
4. | Register the SQLDBG.DLL with regsvr32 SQLDBG.DLL. |
5. | Grant execute permission on the extended stored procedure sp_sdidebug to the debug user. The grant statement is GRANT EXECUTE ON sp_sdidebug to username. |
These steps sound relatively easy to perform, but you will need to know some additional information and have additional skills to perform them. They worked for me the first time, but I was unable to reproduce them on more than one PC.
The best way to complete steps 1 through 4 is to install Visual Studio .NET on the same PC running MSDE. Otherwise you will have to locate copies of these files from other sources. Step 5 requires an available copy of Enterprise Manager or Query Analyzer. You can find the sp_sdidebug stored procedure in the Extended Stored Procedures in the master database. You may use Enterprise Manager to grant execute permissions or run the GRANT statement in step 5 in Query Analyzer.
The preceding five steps are moderately easy to perform; however, when I completed the steps and tried to run a stored procedure on one PC, I received the error message shown in Figure 12.4. The error suggests that the sqldbreg.exe and sqldbg.dll applications were inaccessible. Looking up this error in MSDE resulted in some additional sets of instructions. Part of the instructions suggested that the SQL debugger user must have read and executed NT File System (NTFS) permissions on WINNTSystem32 and Program FilesCommon FilesMicrosoft SharedSQL Debugging. Where were these instructions in the help link for MSDE SQL debugging? Following up on the NTFS permissions issue, I came across instructions for configuring DCOM. The DCOM instructions should be for debugging across devices (remote debugging, where the Visual Studio .NET is on one workstation and the SQL Server instance is on another). Setting the permissions on my workstation and following the DCOM instructions, I figured I would ultimately kill two birds with one stone. Neither resolution seemed to solve the problem.
Figure 12.4. One of the errors you may get while trying to step into a stored procedure.
I consider myself a tenacious problem solver, so I began searching the Web for a resolution. I discovered several articles describing almost precisely what is in the help documentation, but nothing concrete that helped me resolve the particular error shown in Figure 12.4. After several hours I began to wonder how beneficial SQL stored procedure debugging is if it is limited to SQL Server and the process is not straightforward. Do I really want to debug stored procedures by stepping through them, or can I continue to live with debugging stored procedures by inspection and evaluation of the results? I fear that many people will come up with the following answer: If it is too difficult to do, skip it.
The concept of integrated stored procedure debugging is a great one. Configuration needs to be made easier, and the capability should be extended to all database providers.
Configuring SQL Server for Stored Procedure Debugging
During all my efforts I had no particular difficulty in creating, editing, or running stored procedures in the Visual Studio .NET IDE. This is a nice feature; however, getting stored procedure debugging to work for the enterprise version of SQL Server seems to be more difficult than getting the desktop engine to work. Unfortunately, again, the documentation seems insufficient. Below I provide an overview of the precursors for debugging stored procedures in Visual Studio .NET.
1. | You need a SQL Server 6.5 Service Pack 3 or greater instance running on a server. (The standard edition probably will not work here, and the help documentation recommends SQL Server 2000.) |
2. | You need Windows NT 4.0 or greater on the server; again, Windows 2000 is recommended. |
3. | You need Windows 98, ME, NT, or greater on the workstation, but 2000 is recommended. (If you are using Windows 98 or Windows ME, you will need to download, install, and configure DCOM on the workstation.) |
4. | You need to configure Remote Debugging components, which includes several files and service applications. |
5. | You need to configure SQL Server to run under a user account instead of running under the system account. |
6. | You need to run and configure DCOM, which is accomplished by running dcomcnfg.exe. The steps are different for Windows 2000 and XP, but they work toward the same end. Basically, you need to add the SQL Server user account to the Default Access Permissions for DCOM. (Some of the documentation suggests that this should be the SQLDebugger user, but to a limited extent the documentation suggests this should be the user account under which SQL Server is running. In reality this information is not clearly documented.) |
In addition to the preceding precursors, here are a couple of other concerns to address. Several SQL Debugging components will be installed if you install Visual Studio .NET on both client and server, but having a development environment on your database server presents problems of its own. Finally, there are issues related to reinstalling SQL Server, which is something you are likely to do in a test environment, further complicating productive SQL debugging.
If you are anything like me, you might feel that this is too much to ask. Conceptually, debugging stored procedures is a good idea, especially since we can't seem to get away from them altogether. In practice these configuration issues and limitations suggest that this feature needs some work. (If you find a simple and reliable way to configure stored procedure debugging, please let me know at [email protected].)