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.