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 (837221)


The information in this article applies to:

  • Microsoft Visual Studio 2005 Standard Edition
  • Microsoft Visual Studio 2005 Professional Edition
  • Microsoft Visual Studio .NET (2003), Enterprise Architect Edition
  • Microsoft Visual Studio .NET (2003), Enterprise Developer Edition
  • Microsoft Visual Studio .NET (2003), Academic Edition
  • Microsoft Visual Studio .NET (2002), Professional Edition
  • Microsoft Visual Studio .NET (2002), Enterprise Architect Edition
  • Microsoft Visual Studio .NET (2002), Enterprise Developer Edition
  • 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
    1. Click Start, and then click Run.
    2. In the Open box, type odbcad32, and then click OK.

      The ODBC Data Source Administrator dialog box appears.
    3. Click the Connection pooling tab.
    4. 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.
    5. Select Don't pool connections to this driver, and then click OK.
    6. 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_SERVICES

    Note 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:

      "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=-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"

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://msdn.microsoft.com/library/en-us/vsdebug/html/_core_SQL_Debugging_Components.asp

http://msdn.microsoft.com/library/en-us/vsdebug/html/vxlrfSettingUpSQLDebugging.asp

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

Modification Type:MinorLast Reviewed:3/9/2006
Keywords:kbtshoot kbvs2005swept kbvs2005applies kbRegistry kbTSQL kbStoredProc kbSqlClient kbManagedProviderSql kbManaged kbenable kbDebug kbprb KB837221 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.