You cannot debug a SQL Server stored procedure in Visual Studio .NETs (815116)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual Studio .NET (2003), Enterprise Architect Edition
  • Microsoft Visual Studio .NET (2003), Enterprise Developer Edition
  • Microsoft Visual Studio .NET (2003), Professional Edition
  • Microsoft Visual Studio .NET (2003), Academic Edition
  • Microsoft Visual Studio .NET (2002), Enterprise Architect Edition
  • Microsoft Visual Studio .NET (2002), Enterprise Developer Edition
  • Microsoft Visual Studio .NET (2002), Professional Edition
  • Microsoft Visual Studio .NET (2002), Academic Edition
Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

In Microsoft Visual Studio .NET, when you try to debug an ADO.NET application, you cannot step into an associated Microsoft SQL Server stored procedure. You do not receive any error messages. However, the stored procedure window does not appear when you try to debug the stored procedure.

CAUSE

You may notice this behavior if your ADO.NET application already has an active connection to your database. The database drivers that enable SQL debugging do not verify if they have to enable SQL debugging for database connections that are already active. Therefore, if your active connection does not already support SQL debugging, you notice the previously-mentioned behavior.

WORKAROUND

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
To work around this problem, disable connection pooling. The Microsoft .NET Framework Data Provider for SQL Server automatically enables connection pooling for ADO.NET applications. To disable connection pooling, use Registry Editor to change the following registry value from 0xffffffff to 0xfffffffe:

HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\OLEDB_SERVICES

Note Be aware of the following when you make this change:
  • This change is a computer-wide change that disables connection pooling for all applications that are running on your computer.
  • After you disable connection pooling, you may have to restart your application.
  • After you disable connection pooling, the stress on the underlying SQL Server network library may increase if your application frequently opens database connections or closes database connections. Therefore, the performance of your application may be affected.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce the behavior

Note Create an active connection to your database before you follow these steps.
  1. On a computer that is running Windows Server 2003, make sure that IIS is configured to run in non-legacy mode.
  2. Start Microsoft Visual Studio .NET.
  3. On the File menu, point to New, and then click Project.
  4. Click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
  5. Add a Button control from the toolbox to WebForm1.
  6. Add the following code to the top of the code window:
    Imports System.Data.SqlClient
  7. Add the following code to the Click event of the button:

    Note You must change User ID=<username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Dim cn As SqlConnection
        Dim strCn As String
        Dim cmd As SqlCommand
        Dim prm As SqlParameter
        strCn = "Data Source=(local);Initial Catalog=Northwind;" & _
            "User ID=<username>;Password=<strong password>"
        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
            Response.Write("Product ordered: " & dr.GetSqlString(0).ToString & "<BR>")
        End While
        dr.Close()
        cn.Close()
    Modify the SQL Server connection string as appropriate for your environment.
  8. In Project Explorer, right-click the project (not the solution), and then click Properties.
  9. To enable stored procedure debugging, click Configuration Properties, and then click to select the SQL Server Debugging check box.
  10. Set a breakpoint on the following line of code:
    Dim dr As SqlDataReader = cmd.ExecuteReader
  11. In Server Explorer, locate the CustOrderHist stored procedure. Right-click the stored procedure, and then click Edit Stored Procedure.
  12. Set a breakpoint in the stored procedure on the SELECT statement. This appears as one line of executable code.
  13. Press F5 to run the Visual Basic project, and then click the command button. The code runs until it hits the breakpoint that you set before the stored procedure is called.
  14. Press F11. You expect to step into the code of the stored procedure. Instead, the debugger skips the stored procedure and continues with the next line of Visual Basic code.

REFERENCES

For more information about SQL debugging, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/en-us/vsdebug/html/vxlrfSettingUpSQLDebugging.asp


For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

316569 FIX: Cannot Step into Stored Procedure from ASP.NET Code on Windows Server 2003

817178 INFO: Troubleshooting Tips for T-SQL Debugger in Visual Studio

316549 HOW TO: Debug Stored Procedures in Visual Studio .NET

Modification Type:MinorLast Reviewed:3/9/2006
Keywords:kbStoredProc kbDebug kbPerformance kbVisIDDebugger kbprb KB815116 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.