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

Article translations Article translations
Article ID: 252482 - View products that this article applies to.
This article was previously published under Q252482
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SYMPTOMS

The Microsoft ActiveX Data Objects (ADO) technology allows users to obtain a recordset from a data source, "disconnecting" that recordset from its source by setting the Recordset of the ActiveConnection property object to Nothing, and then (optionally) closing the corresponding Connection object. Microsoft highly recommends that you use this feature because it conserves scarce server resources. However, when Microsoft SQL Server is the data source, and the recordset is based on a parameterized ADO Command, SQL Server does not disconnect the user.

You can observe this behavior when you use an ADO Command object as the source of your recordset, whether you use:
  • an ADO Command object that is based on a parameterized stored procedure; for example, the "byroyalty" stored procedure in the sample pubs database,

    -or-
  • an ADO Command object whose CommandText property is a SQL statement that includes parameters. For example, "select au_id from titleauthor where titleauthor.royaltyper = ?" (this is the query that is used in the "byroyalty" stored procedure).
This behavior occurs when you use either Microsoft OLE-DB Provider for SQL Server (SQLOLEDB,) or Microsoft OLE-DB Provider for ODBC drivers (MSDASQL) with theSQL Server ODBC driver.

This behavior does not occur if you use only a Recordset object and call a parameterized stored procedure with an EXECUTE or CALL statement as the Source argument; for example, "EXEC byroyalty 100" or "{CALL byroyalty(100)}."

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack
Because SQL Server releases the database connection after the original ADO Recordset is closed, the workaround is to make a copy of the recordset with the Recordset.Clone method, close the original recordset, and work with the cloned copy.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft Data Access Components 2.6 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start SQL Server Profiler and start a trace. The default trace settings log connection activity.
  2. Run the following Visual Basic code against the SQL Server pubs database:

    Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.
      Dim cn As ADODB.Connection
      Dim cmd As ADODB.Command
      Dim prm As Parameter
      Set cn = New ADODB.Connection
      With cn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=(local);Initial Catalog=pubs;User ID=<username>;Password=<strong password>;"
        .Open
      End With
      Set cmd = New ADODB.Command
      With cmd
        Set .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "byroyalty"
        Set prm = .CreateParameter("", adInteger, adParamInput, , 100)
        .Parameters.Append prm
      End With
      Set rs = New ADODB.Recordset
      With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        Set .Source = cmd
        .Open
      End With
      Set rs.ActiveConnection = Nothing
      cn.Close
      Set cn = Nothing
    					
  3. If you want, add a Debug.Print cn.State statement, or halt the code after the last line and enter ? cn.State in the Immediate window. This action returns 0 (adStateClosed), indicating that the Connection is apparently closed.
  4. Return to the SQL Profiler trace window, and note that SQL Server has not disconnected.

Properties

Article ID: 252482 - Last Review: February 28, 2014 - Revision: 3.1
APPLIES TO
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Data Access Components 2.5
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbmdac260sp1fix kbmdacnosweep KB252482

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com