sql server存储过程的调试

Enabling SQL Debugging on SQL 2000 Desktop Engines

To enable debugging of stored procedures on the SQL 2000 Desktop Engine, you must perform the following steps on the machine where the SQL 2000 Desktop Engine is installed.

To enable debugging with the SQL 2000 Desktop Engine (on a machine with Visual Studio .NET installed)

1. Copy the MSSDI98.DLL from the Microsoft Visual Studio .NET 2003/sqlserver directory to the /binn directory.

To enable debugging with the SQL 2000 Desktop Engine (on a machine without Visual Studio .NET)

1. Locate MSSDI98.DLL on the Visual Studio .NET CD or DVD and copy it to the /binn directory.
2. Locate SQLDBG.DLL on the Visual Studio .NET CD or DVD and copy it to:
   Copy Code

   program files/common files/microsoft shared/sql debugging

3. Reregister SQLDBG.DLL by entering the following command at the Command Prompt:
   Copy Code

   regsvr32 sqldbg.dll

4. Grant execute permission on the stored procedure sp_sdidebug to the debugger user:
   Copy Code

   GRANT EXECUTE
   ON sp_sdidebug 
   TO myUserName  

 

How to debug stored procedures in Visual Studio .NET

View products that this article applies to.

Article ID : 316549 Last Review : May 12, 2007 Revision : 5.2

This article was previously published under Q316549

On This Page

SUMMARY

Option 1: Debug a stored procedure in standalone mode

Option 2: Step into a stored procedure from managed code

Troubleshooting

Limitations of stored procedure debugging

REFERENCES

SUMMARY

This step-by-step article explains two ways that you can debug SQL Server stored procedures and the necessary configuration settings and steps for each approach.

A Visual Studio .NET developer can use the Server Explorer to debug SQL Server stored procedures independently of any Visual Studio project, or the developer can step into the code of the stored procedure directly from managed code in a Visual Basic, Visual C#, or Visual J# project.

Back to the top

Option 1: Debug a stored procedure in standalone mode

1. Open Server Explorer.

NOTE: It is not necessary to add a Data Connection to work with a SQL Server server because SQL Server servers are listed under the Servers node also. You will use the Servers node in the steps that follow; however, you can use a Data Connection to you SQL Server server in the same way. 2. Under the Servers node in Server Explorer, expand the SQL Server machine name, expand the SQL Servers node, expand the SQL Server instance, expand the Northwind database node, and then expand the stored procedures node. 3. Right-click the CustOrderHist stored procedure and then click Step Into Stored Procedure. 4. The Run stored procedure dialog box opens, which lists the parameters of the stored procedure. Type ALFKI as the value for the @CustomerID input parameter and then click OK. 5. In the Visual Studio design environment, a window opens that displays the text of the stored procedure. The first executable line of the stored procedure is highlighted. Press F11 to step through the stored procedure to completion. 6. In the Output window, the following message is displayed, which indicates successful execution:

The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).

Back to the top

Option 2: Step into a stored procedure from managed code

1. Create a new Visual Basic Windows Application project. 2. Drag a Button control from the toolbox to Form1. At the top of the Form1 code window, add the following line of code:

Imports System.Data.SqlClient

3. Copy the following code into the Button1_Click event procedure:

NOTE: Modify the connection string as necessary for your environment.

        Dim cn As SqlConnection
        Dim strCn As String
        Dim cmd As SqlCommand
        Dim prm As SqlParameter
        strCn = "Data Source=(local);Initial Catalog=Northwind;" & _
            "Integrated Security=SSPI"
        cn = New SqlConnection(strCn)
        cmd = New SqlCommand("CustOrderHist", cn)
        cmd.CommandType = CommandType.StoredProcedure
        prm = New SqlParameter("@CustomerID", SqlDbType.Char, 5)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        cmd.Parameters("@CustomerID").Value = "ALFKI"
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader
        While dr.Read
            Console.WriteLine("Product ordered: {0}", dr.GetSqlString(0))
        End While
        dr.Close()
        cn.Close()

4. In Solution Explorer, right-click the project (not the solution) and open the Property pages. Click Configuration Properties in the tree and then click to select the SQL Server Debugging check box on the Debugging page to enable stored procedure debugging. 5. Set a breakpoint on the following line of code:

       Dim dr As SqlDataReader = cmd.ExecuteReader

6. In Server Explorer, locate and open the CustOrderHist stored procedure as described in Option 1. Right-click the stored procedure and then click Edit Stored Procedure. 7. Set a breakpoint in the stored procedure on the SELECT statement, which is the only line of executable code. 8. Press F5 to run the Visual Basic project. 9. When Form1 appears, click the command button. The code will run to the breakpoint that you set before the stored procedure is called. 10. Press F11. Code execution steps from the ExecuteReader method into the stored procedure window. 11. Press F11 again. The single line of code in the stored procedure, the SELECT statement, executes. Then control returns to your Visual Basic project, and the project runs to completion. 12. To continue to step through the Visual Basic code after you step out of the stored procedure, you must set a second breakpoint in the Visual Basic code after the call to the stored procedure. For example, in the sample code shown in this section, you can set the second breakpoint on the following line:

        While dr.Read

Back to the top

Troubleshooting

• To step from Visual Studio code into a stored procedure, you must enable SQL Debugging in the Project Properties on the Debugging page. • To step through stored procedure code, you must set a breakpoint in the stored procedure itself. Otherwise, debugging steps over the stored procedure and the window for the stored procedure does not open. • To continue to step through Visual Studio code after debugging steps out of a stored procedure, you must set a breakpoint in the project code at a point after the execution of the stored procedure. Otherwise, the code runs to completion after debugging steps out of the stored procedure. • For setup and configuration issues, refer to the section entitled "Setting Up SQL Debugging" in the Visual Studio .NET documentation.

Back to the top

Limitations of stored procedure debugging

The following is a list of limitations that you may encounter when you debug stored procedures and that you do not encounter when you debug Visual Studio code: • You cannot "break" execution. • You cannot "edit and continue." • You cannot change the order of statement execution. • Although you can change the value of variables, your changes may not take effect because the variable values are cached. • Output from the SQL PRINT statement is not displayed.

Back to the top

REFERENCES

For more information about debugging in Visual Basic 6.0, click the following article number to view the article in the Microsoft Knowledge Base:

170496 (http://support.microsoft.com/kb/170496/) Tips for debugging stored procedures from Visual Basic

For more information, visit the following Microsoft Web site (or see the "Debugging SQL" topic in the Visual Studio .NET Help documentation):

Debugging SQL
http://msdn2.microsoft.com/en-us/library/zefbf0t6(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/zefbf0t6(vs.71).aspx)

Back to the top

---

APPLIES TO

• Microsoft ADO.NET (included with the .NET Framework) • Microsoft ADO.NET 1.1 • Microsoft Visual J# .NET 2003 Standard Edition • Microsoft Visual Basic .NET 2002 Standard Edition • Microsoft Visual Basic .NET 2003 Standard Edition • Microsoft Visual C# .NET 2002 Standard Edition • Microsoft Visual C# .NET 2003 Standard Edition • Microsoft Visual Studio .NET 2003 Enterprise Architect • Microsoft Visual Studio .NET 2003 Enterprise Developer

Back to the top

Keywords: 

kbhowtomaster kbsqlclient kbsystemdata KB316549

Back to the top