BUG: Disconnected Hierarchical Recordset Does Not Disconnect from Database Server

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.

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.

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.

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

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.

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