You cannot access a SQL Server database by using the OLE DB provider for SQL Server when your application is in a high-stress scenario

Article translations Article translations
Article ID: 907264 - View products that this article applies to.
Bug #: 245183 (SQLBUDT)
Expand all | Collapse all

SYMPTOMS

Consider the following scenario. A large enterprise application uses Microsoft SQL Server. The application is in a high-stress scenario. When the application tries to access a SQL Server database by using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB),you may receive one or more of the following error messages:
General Network Error
SQL Server Does Not Exist Or Access Denied
Timeout Expired

CAUSE

This issue occurs when a large enterprise application in a high-stress scenario runs many queries on OLE DB sessions or on ActiveX Data Objects (ADO) connections that use SQLOLEDB.

For example, the following behavior may occur:
  • A new SQL query uses a firehose cursor.

    Note A firehose cursor is a server-side, forward-only, and read-only cursor.
  • The new SQL query is executed on an OLE DB session or on an ADO connection that uses SQLOLEDB.
  • The OLE DB session or the ADO connection is already busy processing result sets from a previously existing query.
When this behavior occurs, SQLOLEDB creates a new non-pooled implicit connection to run the new SQL query.

When a large enterprise application is in a high-stress scenario, running many of these new SQL queries creates many corresponding non-pooled implicit connections. Therefore, you may receive "General Network Error" error messages.

You may receive these error messages because one or more of the following conditions are true:
  • The SQL Server WinsockListenBacklog registry setting sets the maximum number of simultaneous new login requests. However, the number of simultaneous new login requests from the new non-pooled implicit connection to the computer that is running SQL Server exceeds this value.
  • The MaxUserPort registry setting limits the number of ephemeral TCP/IP ports on the application computer. However, the application requires more ephemeral TCP/IP ports to be assigned by Windows Sockets for the new non-pooled implicit connections.
  • The TCPTimedWaitDelay registry setting prevents TCP/IP socket ephemeral ports on the application computer from recycling fast enough to meet the demand for new implicit TCP/IP connections for the new non-pooled implicit connections.

WORKAROUND

To work around this issue, use one of the following methods:
  • Avoid implicit connections in large enterprise application scenarios. Instead, explicitly create additional OLE DB data source/session pairs, or explicitly create ADO connections.
  • Avoid using the firehose cursor on a SQLOLEDB session or an ADO connection that will process multiple result sets at the same time.
  • Set the multiple connections property of the ADO connection to false, or set the OLE DB DBPROP_MULTIPLECONNECTIONS property to VARIANT_FALSE.
  • Use the SQL Server 2005 Native Client API. SQL Server 2005 Native Client supports Multiple Active Result Sets (MARS). MARS maintains multiple result sets over the same OLE DB session or ADO connection.

    For more information about SQL Server 2005 Native Client and MARS, visit the following Microsoft Developer Network (MSDN) Web sites:
    Microsoft SQL Server Native Client
    http://msdn2.microsoft.com/en-us/data/aa937733.aspx

    Using Multiple Active Result Sets (MARS)
    http://msdn2.microsoft.com/en-us/library/ms131686.aspx

    Using ADO with SQL Native Client
    http://msdn2.microsoft.com/en-us/library/ms130978.aspx

STATUS

This behavior is by design.

MORE INFORMATION

For more information about implicit connections and SQLOLEDB, click the following article numbers to view the articles in the Microsoft Knowledge Base:
271128 Implicit connections created by the SQL Server OLE DB Provider (SQLOLEDB) are not pooled
194979 ADO spawns additional connections to SQL Server
For more information about connection pooling, click the following article number to view the article in the Microsoft Knowledge Base:
328476 Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled

Properties

Article ID: 907264 - Last Review: March 14, 2007 - Revision: 1.2
APPLIES TO
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 1.5
Keywords: 
kbdesign kbnofix kbtshoot kbprb KB907264
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com