Microsoft KB Archive/907264

= 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 ID: 907264

Article Last Modified on 3/14/2007

-

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

-



Bug #: 245183 (SQLBUDT)



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 &quot;General Network Error&quot; 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

Keywords: kbtshoot kbnofix kbprb kbdesign KB907264

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.