Help and Support
 

powered byLive Search

BUG: Disconnected Hierarchical Recordset Does Not Disconnect from Database Server

View products that this article applies to.
Article ID:288409
Last Review:November 5, 2003
Revision:3.0
This article was previously published under Q288409
On This Page

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.

Back to the top

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.

Back to the top

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.

Back to the top

STATUS

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

Back to the top

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.

Back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
252482 (http://support.microsoft.com/kb/252482/EN-US/) BUG: ADO Disconnected Recordset That Uses Parameterized Query Is Not Disconnected by SQL Server

Back to the top

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

Back to the top

Keywords: 
kbbug kbdatabase kbclient kbpending KB288409

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.