ID: Q194979
The information in this article applies to:
Anyone who has used Remote Data Objects (RDO) to communicate with a SQL Server database may recognize the following error message:
Connection is busy with results from another hstmt.
ActiveX Data Objects (ADO) hides many the complexities of communicating with your database and makes writing code to query your database easy. For example, if you run two asynchronous queries that generate cursors against the same ADO connection object, those queries are queued. After the first asynchronous query completes, the second one executes. Requesting two firehose cursors on the same ADO connection object creates two actual connections to the database.
Actually, this behavior is controlled by the OLE DB provider used to communicate with SQL Server, not by ADO itself. ADO asks the provider (either the native SQL Server OLE DB provider or the default provider that communicates to ODBC, depending on how you are connecting to SQL Server) to run the query and the provider determines whether another connection to the database is required. If so, the provider creates that new connection. It is possible for you to use one ADO connection object but have multiple connections to your SQL Server database open.
The more you understand this behavior, the better you will be able to optimize your ADO code. For example, if you open two recordset objects by setting the connection string in the ActiveConnection property, you create two separate connections to your database.
NOTE: This behavior is not specific to SQL Server and ADO controls this behavior.
The best way to determine how many connections your application is actually making to SQL Server is to use SQL Server's utilities such as SQL Trace or Performance Monitor while you run your application.
Following are some guidelines:
The ADO client cursor engine uses firehose cursors when retrieving data from the server for optimal performance. The client cursor engine maintains this data in its own cursor rather than taking up resources from the server or the provider. If you perform an asynchronous query using the ADO client cursor engine, the actual connection to SQL Server is essentially blocked until that query completes and ADO retrieves all of the results. Therefore, if you try to use the ADO connection object while this query is still running you will be creating a second connection.
You can have multiple non-firehose cursors open on a connection and retrieve data from any of them. However, if you have a non-firehose cursor open and you then open a firehose cursor, you receive a second connection to your database. The provider establishes the second connection to provide you with as much flexibility as possible for working with your recordset objects. This way you can still work with your non-firehose cursor and your firehose cursors. If the provider creates the firehose cursor on the same actual connection to the database, you would not be able to retrieve any more information from SQL Server until you retrieve all of the data from the firehose cursor.
In addition, if you open a firehose cursor, that connection to the database is now tied up until you retrieve all of the data from that cursor. This means that if you use a single ADO connection object to open a firehose cursor and then a non-firehose cursor, the second (non-firehose) cursor opens on a second connection to the database. If you first retrieve all of the data from the firehose cursor, then the second (non-firehose) cursor is opens on the same connection to the database.
Additional query words: kbADO200 kbADO150 kbOLEDB
Keywords : kbADO200
Version : WINDOWS:1.5,2.0,5.0,6.0
Platform : WINDOWS
Issue type : kbinfoLast Reviewed: November 4, 1998