Microsoft KB Archive/288409

= BUG: Disconnected Hierarchical Recordset Does Not Disconnect from Database Server =

Article ID: 288409

Article Last Modified on 11/5/2003

-

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.



Steps to Reproduce Behavior
The following steps use Microsoft Visual Basic and the Microsoft SQL Server Northwind sample database:  Enter the following code in an event procedure in a Visual Basic project, with a reference to ADO. Be sure to declare a recordset variable &quot;rst&quot; 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.  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 = and password = to the correct values before you run this code. Make sure that  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 &quot;Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;User ID=;Password= ;&quot;

strSQL = &quot;SHAPE {SELECT CUSTOMERID, COMPANYNAME FROM Customers} &quot; & _ &quot;APPEND({SELECT ORDERID, CUSTOMERID, ORDERDATE FROM Orders} &quot; & _   &quot;RELATE CUSTOMERID TO CUSTOMERID) AS CustOrders&quot; Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open strSQL, conn, adOpenStatic, adLockBatchOptimistic Set rst.ActiveConnection = Nothing conn.Close Debug.Print &quot; After closing: &quot; & conn.State Set conn = Nothing  Run the project and leave the project running (that is, the module-level recordset variable is still in scope).</li> 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.</li></ol>

<div class="references_section">