Microsoft KB Archive/815116

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

Article ID: 815116

Article Last Modified on 5/18/2007

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Professional Edition
 * Microsoft Visual Studio .NET 2003 Academic Edition
 * Microsoft Visual Studio .NET 2002 Enterprise Architect
 * Microsoft Visual Studio .NET 2002 Enterprise Developer
 * 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:

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.



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

Note You must change User ID=  and 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 = &quot;Data Source=(local);Initial Catalog=Northwind;&quot; & _ &quot;User ID= ;Password= &quot; cn = New SqlConnection(strCn) cmd = New SqlCommand(&quot;CustOrderHist&quot;, cn) cmd.CommandType = CommandType.StoredProcedure prm = New SqlParameter(&quot;@CustomerID&quot;, SqlDbType.Char, 5) prm.Direction = ParameterDirection.Input cmd.Parameters.Add(prm) cmd.Parameters(&quot;@CustomerID&quot;).Value = &quot;ALFKI&quot; cn.Open Dim dr As SqlDataReader = cmd.ExecuteReader While dr.Read Response.Write(&quot;Product ordered: &quot; & dr.GetSqlString(0).ToString & &quot;<BR>&quot;) End While dr.Close cn.Close Modify the SQL Server connection string as appropriate for your environment. </li> In Project Explorer, right-click the project (not the solution), and then click Properties.</li> To enable stored procedure debugging, click Configuration Properties, and then click to select the SQL Server Debugging check box.</li>  Set a breakpoint on the following line of code: Dim dr As SqlDataReader = cmd.ExecuteReader </li> In Server Explorer, locate the CustOrderHist stored procedure. Right-click the stored procedure, and then click Edit Stored Procedure.</li> Set a breakpoint in the stored procedure on the SELECT statement. This appears as one line of executable code.</li> 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.</li> 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.</li></ol>

<div class="references_section">