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.
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.
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 registryThrough the calling application
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:
If you use this method, you may have to restart the client computer.
For information about other settings, see the OLEDB documentation.
- 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:
"Data Source=MySQLServer;Initial Catalog=MyDatabase;Password=MyPassword; User ID=MyUserName;Pooling=False;"Note In this sample connection string, MySQLServer, MyDatabase, MyPassword, and MyUserName are 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=-4For 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”
This behavior is by design.
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.
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.
For more information about SQL debugging components and about how to set up SQL debugging, visit the following Microsoft Developer Network (MSDN) Web sites:
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
SQL Server ODBC driver resets connection attributes when used in connection pooling
How to enable connection pooling in an OLE DB application
You cannot debug a SQL Server stored procedure in Visual Studio .NET
"Timeout expired" error message when you run a Visual Studio .NET 2003 application
Article ID: 837221 - Last Review: November 27, 2007 - Revision: 2.6
- 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
|kbtshoot kbvs2005swept kbvs2005applies kbregistry kbtsql kbstoredproc kbsqlclient kbmanagedprovidersql kbmanaged kbenable kbdebug kbprb KB837221|