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

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.

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.

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.

This behavior is by design.

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.

For more information about SQL debugging, visit the following Microsoft Developer Network (MSDN) Web site:
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: