Microsoft KB Archive/837221

= You cannot debug a SQL Server stored procedure more than one time while debugging managed .NET Framework code in Visual Studio 2005 or in Visual Studio .NET =

Article ID: 837221

Article Last Modified on 11/27/2007

-

APPLIES TO


 * Microsoft Visual Studio 2005 Standard Edition
 * Microsoft Visual Studio 2005 Professional Edition
 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Academic Edition
 * Microsoft Visual Studio .NET 2002 Professional Edition
 * Microsoft Visual Studio .NET 2002 Enterprise Architect
 * Microsoft Visual Studio .NET 2002 Enterprise Developer
 * Microsoft Visual Studio .NET 2002 Academic Edition
 * Microsoft ADO.NET 2.0

-





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
You may not be able to debug a Microsoft SQL Server stored procedure more than one time from the native code or from the managed .NET Framework code if the following conditions are true:
 * The connection pooling feature is enabled for the SQL Server database connection that your application is using.
 * Your application is using a reopened connection from the connection pool.



CAUSE
Connection pooling is a technique to improve application performance. When the application closes its data connection, the connection to the computer that is running SQL Server is not completely closed. The connection is held in a pool so that the connection can be reused if the application tries to reopen the connection later. However, when a connection is re-established through connection pooling, SQL debugging is not re-enabled.



WORKAROUND
To work around this behavior, turn off the connection pooling feature. To do this, use one of the following methods:  Through the ODBC Data Source Administrator  Click Start, and then click Run. In the Open box, type odbcad32, and then click OK.

The ODBC Data Source Administrator dialog box appears. Click the Connection pooling tab. Scroll to locate the SQL Server in the Name column of the ODBC Drivers list, and then double-click SQL Server.

The Set Connection Pooling Attributes dialog box appears. Select Don't pool connections to this driver, and then click OK.</li> In the ODBC Data Source Administrator dialog box, click OK.</li></ol> </li> Through the registry

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.

The Microsoft .NET Framework Data Provider for SQL Server automatically turns on connection pooling for Microsoft ADO.NET applications.

To turn off connection pooling, use Registry Editor to change the following registry value from 0xffffffff to 0xfffffffe:

Note If you use this method, you may have to restart the client computer.

For information about other settings, see the OLEDB documentation.</li> Through the calling application <ul> To control connection pooling, set the Pooling keyword in the connection string that is used to connect to the computer that is running SQL Server. For example, use the following connection string to turn off connection pooling for your computer that is running SQL Server:

&quot;Data Source= ;Initial Catalog= ;Password= ; User ID= ;Pooling=False;&quot;

Note In this sample connection string,,  ,  , and   are placeholders for the corresponding values for your system.</li> The OLEDB native provider handles connection pooling in OLEDB. By default, connection pooling is turned on. Therefore, you do not have to do anything to use connection pooling. However, if you are working with a database that does not support connection pooling, such as Microsoft Access, you can turn off connection pooling without editing the registry. To do this, use the following connection string keyword:

OLE DB Services=-4

For example, you can use the following connection string:

“Provider=SQLOLEDB.1; OLE DB Services=-4; Password= ; User ID= ; Initial Catalog= ; Data Source= ”

</li></ul> </li></ul>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
Some more connection string keywords used for controlling the connection pooling:
 * Max Pool Size: By default, the value is 100. You may want to increase this value for enterprise applications.
 * Min Pool Size: By default, the values is 0. Typically, you do not have to increase this value.
 * Enlist: When this value is set to True, the connections from the pool are enlisted in the current distributed transaction context if a distributed transaction context exists.

<div class="references_section">