BUG: Disconnected Hierarchical Recordset Does Not Disconnect from Database Server (288409)


The information in this article applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q288409

SYMPTOMS

If you open a hierarchical ActiveX Data Objects (ADO) recordset by using the Microsoft Data Shaping Service for OLE DB (MSDataShape) provider, set the Recordset's ActiveConnection = Nothing to disconnect the recordset, and then close the ADO Connection object, ADO reports that the Connection.State = 0 (adStateClosed).

However, if you then check open connections on the database server (for example, by checking the SYSPROCESSES table on SQL Server when you use SQLOLEDB, or by checking the V$SESSION table on Oracle when you use MSDAORA), you see that the connection to the database server remains open.

CAUSE

The ADO Client Cursor Engine (CCE) is not calling the appropriate method on the MSDataShape provider to indicate that the recordset is being disconnected and to release the reference held to the OLE DB Command object.

RESOLUTION

As long as a shaped ADO Recordset object remains in the client application (that is, as long as it has not explicitly been destroyed or gone out of scope), the Recordset's Connection remains open on the database server. As a developer, you must understand and address the possible ramifications of this behavior.

As a workaround, you can persist the Recordset to another format (to XML, to ADTG format, or to a Stream), and then close the original Recordset to release the connection and reload the persisted data into a new, disconnected Recordset.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

The following steps use Microsoft Visual Basic and the Microsoft SQL Server Northwind sample database:
  1. Enter the following code in an event procedure in a Visual Basic project, with a reference to ADO.
  2. Be sure to declare a recordset variable "rst" at module level, not procedure level, because as long as the recordset object exists, the connection remains open on the server. If the recordset goes out of scope and is destroyed, the connection is released.
  3. Use SELECT COUNT(*) FROM SYSPROCESSES in Query Analyzer to check the number of SQL Server processes before running this code.

    Note You must change User ID =<UID> and password =<strong password> to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.
    Dim conn As ADODB.Connection
  Dim strSQL As String
    
  Set conn = New ADODB.Connection

  conn.Open "Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;User ID=<UID>;Password=<strong password>;"

  strSQL = "SHAPE {SELECT CUSTOMERID, COMPANYNAME FROM Customers} " & _
    "APPEND({SELECT ORDERID, CUSTOMERID, ORDERDATE FROM Orders} " & _
    "RELATE CUSTOMERID TO CUSTOMERID) AS CustOrders"
    
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
  rst.Open strSQL, conn, adOpenStatic, adLockBatchOptimistic
  Set rst.ActiveConnection = Nothing
    
  conn.Close
  Debug.Print " After closing: " & conn.State
  Set conn = Nothing
  4. Run the project and leave the project running (that is, the module-level recordset variable is still in scope).
  5. Use SELECT COUNT(*) FROM SYSPROCESSES in Query Analyzer to check the number of SQL Server processes after running this code. Note that you expect to see the same number of processes (connections) as before running the project because you disconnected the recordset. In fact, you see one additional process; the MSDataShape connection has not been released on the database server.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

252482 BUG: ADO Disconnected Recordset That Uses Parameterized Query Is Not Disconnected by SQL Server

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kbClient kbDatabase kbpending KB288409
©2003 Microsoft Corporation. All rights reserved.