Microsoft SQL Server 7.0: Stored Procedure Debugging How-To

  • Article

 

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®, Microsoft Visual InterDev® includes a SQL debugger that you can use to debug Microsoft 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:

  • You must have the Enterprise Edition of Visual Studio.
  • You must be running SQL Server 6.5 with Service Pack 2 or later. SQL 7.0 is recommended.
  • SQL Server must be running Microsoft Windows NT® 4.0 or later.
  • Your workstation must be running Windows® 95 or Windows NT 4.0 or later.

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

  • Make sure that you have installed SQL debugging components on your SQL Server.
  • Establish a Windows NT user who has administration privileges on the server computer where SQL Server is running.
  • Configure Distributed COM (DCOM) on the server for SQL debugging.
  • Make sure the DCOM configuration on the client supports SQL debugging (Windows 95 workstations only).

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.
    • If the server is already installed, under Add/Remove Options, choose Server Applications and Tools.
    • If no other components have been installed, proceed through the Setup Wizard until you get to the page offering Enterprise Setup Options. Choose Server Applications.
  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:
    • SQL Server Debugging
    • MS Data Access Components
    • Visual InterDev Server
  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

  • In the Data View window, right-click the stored procedure to debug, and then choose Debug.

    The Editor window opens with the stored procedure text in it and the debugging commands enabled on the Debug menu.

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:

  • The Auto and Immediate windows are not functional while you are debugging stored procedures. Although you can display them, the Auto window will be empty and the Immediate window will not allow you to enter expressions.
  • You cannot change the execution sequence using the Set Next Statement command.

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.