Microsoft KB Archive/271128

= PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled =

Article ID: 271128

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 7.01
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q271128





SYMPTOMS
When the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) creates implicit connections, these connections are not pooled.

The provider creates implicit connections under the following two situations:
 * If you create additional sessions from the same data source object while keeping the first session open, the provider creates implicit connections to the SQL Server for each additional session.
 * If you execute multiple commands from the same connection, and you are specifying read-only, forward-only (default resultset) cursors. Because SQL Server doesn't allow more than one pending resultset per connection per default-resultset (also known as firehose) cursor, the provider will implicitly create another connection if DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE (the default).

In both cases, the implicit connection, which is created by the provider, is not pooled. This can lead to poor performance compared to opening and closing the connection explicitly. For more information on this behavior, see the article &quot;How Consumers Use Resource Pooling&quot; at the following MSDN Web site:

http://msdn.microsoft.com/library/psdk/dasdk/msda3753.htm



CAUSE
Because the SQL Server OLE DB provider doesn't permit more than one set of results to be pending on a connection where the results are being returned by means of a forward-only, read-only (default-resultset) cursor, the provider needs to create an additional SQL Server connection to execute a second command on the connection. The provider will only do this implicitly if the Data Source property DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE.



RESOLUTION
To ensure pooling, you must create additional data source/session pairs, or explicitly create ActiveX Data Objects (ADO) connections. By explicitly creating the ADO connection or OLE DB Session, you can be guaranteed that the connection will be pooled by OLE DB, which means that the next request for an ADO connection or OLE DB Session can reuse an existing session and SQL Server connection.



MORE INFORMATION
The following Microsoft Visual Basic code demonstrates a situation in which an implicit SQL Server connection is created by the SQL Server OLE DB Provider. The implicit connection that is created by the second Execute command is not pooled. Dim conn As New Connection Dim cmd1 As New Command Dim cmd2 As New Command Dim rs As Recordset

conn.Open &quot;Provider=SQLOLEDB;Data Source=YourDb;Initial Catalog=pubs&quot;, &quot;sa&quot;

' NOTE: If the following code were uncommented, the SQL Server Provider would generate an error ' rather than create any implicit connections. 'conn.Properties(&quot;Multiple connections&quot;) = False

' Share the connection for the command objects. cmd1.ActiveConnection = conn cmd2.ActiveConnection = conn cmd1.CommandText = &quot;Select * from authors&quot; cmd2.CommandText = &quot;Select * from titleview&quot;

' Execute the first command and assign results to a recordset. Set rs = cmd1.Execute

' Because results are pending on the same connection from the first command which ' executed, the next statement will cause the SQL Server OLE DB provider to create a new ' SQL Server connection to execute the SQL statement and get its set of results. ' If the following statement, which clears the results from the first execution, is run before the ' next Execute call, an implicit connection would not be created. '   Set rs = Nothing cmd2.Execute

Additional query words: resource pooling session

Keywords: kbprovider kbdatabase kbprb KB271128

-

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

© Microsoft Corporation. All rights reserved.