BUG: SQLOLEDB May Spawn Additional Connection For Action Query

• Microsoft OLE DB, version 2.0
• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
• Microsoft Visual Studio versions 6.0, 97

SYMPTOMS

If you have a server-side cursor open and execute an action query, the SQL Server OLE DB Provider treats an action query as if it returned data in a forward-only, read-only cursor and isolates that query on its own connection. This connection is created solely for the action query and is closed immediately after executing the query.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

If you do not have a server-side cursor currently opened on the ActiveX Data Objects (ADO) connection object, the SQL Server OLE DB provider does not create a second connection to the database.

Use SQL Server's SQL Trace utility to see that the following code establishes a second connection to the database to perform the action query. Modify the connection string to connect to your SQL Server database.

Here is the code:

   Dim cnPubs As New ADODB.Connection
   Dim rsKeyset As New ADODB.Recordset
   Dim strConn As String
   Dim strSQL As String
   Dim strActionQuery As String

   strConn = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=pubs;"
   strSQL = "SELECT * FROM Authors"
   strActionQuery = "DELETE FROM Authors WHERE 1 = 0"

   cnPubs.Open strConn, "sa", ""
   rsKeyset.Open strSQL, cnPubs, adOpenKeyset, adLockReadOnly, adCmdText
   cnPubs.Execute CommandText:=strActionQuery, Options:=adExecuteNoRecords 

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by David Sceppa, Microsoft Corporation

Additional query words:

Keywords : kbADO200 kbADO200bug kbDatabase kbOLEDB kbOLEDB200bug kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbADO210sp2bug
Version : WINDOWS:2.0,2.1 SP2,5.0,6.0,97
Platform : WINDOWS
Issue type : kbbug