Debugging a SQL Server Stored Procedure

Problem

Given an application that uses a SQL Server stored procedure that is causing errors, you need to debug the stored procedure.

Solution

Use Visual Studio .NET to debug SQL Server stored procedures (in both standalone mode and from managed code).

Discussion

Debugging a stored procedure in standalone mode

You can debug a stored procedure in standalone mode from Visual Studio .NET Server Explorer by following these steps:

  1. Open the Server Explorer window in Visual Studio .NET by selecting it from the View menu.
  2. Create a connection to the database or select an existing connection.
  3. Select and expand the node for the database that contains the stored procedure.
  4. Expand the Stored Procedures node.
  5. Right-click on the stored procedure to be debugged and select Step Into Stored Procedure from the popup menu.
  6. If requested , supply the parameter values on the Run Stored Procedure dialog.

Alternatively, if the stored procedure is already open in a source window in Visual Studio .NET:

  1. Right-click on the stored procedure to be debugged and select Step Into Stored Procedure from the popup menu.
  2. If requested, supply the parameter values on the Run Stored Procedure dialog.

Debugging a stored procedure from managed code

To debug a stored procedure from managed code, SQL debugging must be enabled for the project. Follow these steps:

  1. Open the solution.
  2. In the Solution Explorer window, select the project and right-click. Select Properties from the popup menu.
  3. In the Property Pages dialog, select Debug from the Configuration drop-down list box.
  4. Select the Configuration Properties folder in the left pane and choose Debugging.
  5. In the Debuggers section of the right pane, set Enable SQL Debugging to true .
  6. Click OK to close the dialog.

Table 9-2 lists the components that must be installed for SQL Server debugging.

Table 9-2. SQL Server debugging components

Component

Installation location

SQLLE.DLL

Client

SQLDBG.DLL

Client and server

MSSDBI98.DLL

Server in the inn directory of the SQL Server instance

SQLDBREG2.EXE

Client

There are some other significant limitations to SQL Server Debugging:

For more information about debugging SQL stored procedures, see the topic "Debugging SQL" in the MSDN Library.

Категории