Microsoft SQL Server 7.0: Stored Procedure Debugging How-To

Microsoft Corporation

October 1998

Summary: Describes how to use the SQL debugger included with Microsoft® Visual InterDev™ to debug Microsoft SQL Server™ stored procedures. (4 printed pages)

Debugging Stored Procedures and Triggers with Visual InterDev

If you are working with the Enterprise Edition of Microsoft Visual Studio®, Visual InterDev includes an SQL debugger that you can use to debug SQL Server stored procedures and triggers in much the same way that you debug other kinds of scripts or programs. However, there are some differences in how you set up SQL debugging and how the debugger runs.

Setting Up SQL Debugging

SQL debugging requires the following:

To use SQL debugging, you must properly configure your server and workstation. You should:

Installing SQL Server Debugging Components

SQL debugging requires components that you install on your SQL Server. These components are part of Visual Studio, Enterprise Edition.

To install SQL debugging components

  1. On the computer where SQL Server is installed, start the Visual Studio, Enterprise Edition Setup program.

  2. The Installation Wizard presents slightly different choices, depending on whether you have previously installed server components on the computer.
  3. Proceed through the Setup Wizard until you get to the page offering Enterprise Setup Options. Choose Server Applications.

  4. On the next page, select Launch BackOffice Installation Wizard, and then choose Install.

  5. When the BackOffice Business Solutions Wizard is displayed, choose Custom, and then choose Next.

  6. Proceed until you see the page offering a list of components to install. Clear the check box on all components except the following:
  7. Proceed with the installation.

Setting Up a Debug User

To use SQL debugging, you must be able to provide the name and password of a Windows NT user who has administration privileges on the server computer where SQL Server is running.

To set up a user for SQL debugging

  1. In Windows Control Panel on the server, choose Settings, and then choose Services.

  2. Select MSSQLServer, and then choose Startup.

  3. Check the Log On As settings. If the option is set to System Account, change it to This Account, enter the valid domain and user account (in the form domain\account) of a user with administration privileges, and then enter the password.

  4. If you have changed the setting, restart SQL Server.

Setting Up DCOM for SQL Debugging

If you can, keep a local copy of your database on your machine; then you won't need to use DCOM to do cross-machine debugging.

SQL debugging uses Distributed COM (DCOM) to communicate between your client computer and the database server. You must therefore configure DCOM to allow a remote user to attach the debugger to a process there.

By default, the correct DCOM settings are in place when SQL Server is installed on the server. However, because of security considerations for the computer running SQL Server, you might want to restrict access to debugging. Use the following as a general procedure for setting up DCOM for the SQL Server computer.

To configure DCOM on the server for SQL debugging

  1. From the Windows Start menu on the server, choose Run, and then in the Open box, type Dcomcnfg.exe at the prompt.

  2. In the Distributed COM Configuration Properties window, choose the Default Security tab. Under Default Access Permissions, choose Edit Default.

  3. If the group Everyone does not already have permissions, choose Add, and then add the domain and user account (in the form domain\account) of a user with administration privileges.

  4. After adding the account, check for SYSTEM. If it is not already on the list, add it by choosing it from the list of users in the Add Names and Groups dialog box.

  5. If you have changed any of the settings described in this procedure, restart SQL Server.

Note   If you added your account to the remote server but the current account on the remote server has not been added, that account cannot debug even if a user with that account name is running Visual InterDev on the server computer.

Running SQL Debugging

Unlike debugging other types of processes, you don't debug stored procedures or triggers while they are running. Instead, you open the procedure in the editor and debug it from there.

To debug a stored procedure

After you've opened the Editor window in Debug mode, you can use debugger commands as usual. For example, you can set breakpoints and step through the procedure. You can view the values of variables and passed parameters in the Locals window. You can also drag expressions to the Watch window to track them as you step through or run the procedure. The results of SQL PRINT statements are displayed in the Output window.

However, the SQL debugger has the following differences from the debugger used for script:

If you are working with simple SELECT statements—ones that return only a single value—the return value is in a variable that you can inspect in the Locals window. However, if the SELECT statement returns a result set, it is not displayed in the debugger. Instead, you can view the result set in the Output window.