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.
- In the ODBC Data Source Administrator dialog box, click OK.
- 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:HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\OLEDB_SERVICESNote If you use this method, you may have to restart the client computer.
For information about other settings, see the OLEDB documentation.
- Through the calling application
- 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:
Note In this sample connection string,
"Data Source=
MySQLServer;Initial Catalog=MyDatabase;Password=MyPassword; User ID=MyUserName;Pooling=False;"MySQLServer,MyDatabase,MyPassword, andMyUserNameare placeholders for the corresponding values for your system. - 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=
MyPassword; User ID=MyUserName; Initial Catalog=MyDatabase; Data Source=MySQLServer”
- 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:
STATUS
This behavior is by design.
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.
REFERENCES
For more information about SQL debugging components and about how to set up SQL debugging, visit the following Microsoft Developer Network (MSDN) Web sites:
http://msdn2.microsoft.com/en-us/library/Aa292301(VS.71).aspx
http://msdn2.microsoft.com/en-us/library/s4sszxst(vs.71).aspx
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
318606 SQL Server ODBC driver resets connection attributes when used in connection pooling
166083 How to enable connection pooling in an OLE DB application
815116 You cannot debug a SQL Server stored procedure in Visual Studio .NET
830118 "Timeout expired" error message when you run a Visual Studio .NET 2003 application
Keywords: kbtshoot kbvs2005swept kbvs2005applies kbregistry kbtsql kbstoredproc kbsqlclient kbmanagedprovidersql kbmanaged kbenable kbdebug kbprb KB837221
