Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 271128 - Last Review: December 5, 2003 - Revision: 3.1
PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled
This article was previously published under Q271128
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 "How Consumers Use Resource Pooling" at the following MSDN Web site:
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.
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.
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 "Provider=SQLOLEDB;Data Source=YourDb;Initial Catalog=pubs", "sa"
' NOTE: If the following code were uncommented, the SQL Server Provider would generate an error
' rather than create any implicit connections.
'conn.Properties("Multiple connections") = False
' Share the connection for the command objects.
cmd1.ActiveConnection = conn
cmd2.ActiveConnection = conn
cmd1.CommandText = "Select * from authors"
cmd2.CommandText = "Select * from titleview"
' 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
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
| kbdatabase kbprb kbprovider KB271128 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate