PRB: Single SQL Server Connection Can Support Only One Active Firehose Cursor (258697)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft SQL Server 7.0
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.1 SP1
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
This article was previously published under Q258697

SYMPTOMS

SQL Server can support only one active firehose (server-side, forward-only, read-only) recordset per connection. If you try to open a second firehose ADO recordset on an ADO connection before processing or looping through all the records of the original firehose recordset, ADO silently opens an additional database connection to SQL Server. This additional connection is opened to manage the second ADO firehose recordset.

CAUSE

A single SQL Server connection can only support one active command. While a firehose cursor is the fastest means to get a Recordset from the server, it does so by not creating a cursor. The effect of not creating a cursor forces the connection to be dedicated between the client and server until all the records have been fetched. Since a cursor was not established, there is no way to maintain the current record in the Recordset and, as a result, the connection is forced to service the request for the firehose cursor until the end of file (EOF) has been reached. You can change the behavior by specifying a cursor, but this incurs the overhead of maintaining a cursor, which is typically not all that great in comparison to the build fetch.

The information in this article applies to SQL Server, Sybase, and a few other database systems that support only one active command per connection. It does not apply to Jet or Oracle, because both of these support multiple active statements per connection.

NOTE: The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

MORE INFORMATION

Steps to Reproduce Behavior

The following example is based on the Sample SQL Server 7.0 Northwind database. You need to modify the ADO connection string to point to your installation of SQL Server and supply the required authentication information.
  1. Open a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. Set a Project reference to the Microsoft ActiveX Data Objects 2.X Library.
  3. Drag and drop two CommandButtons on to Form1.
  4. Name the first CommandButton cmdOpenRecordsets and set its Caption property to Open Firehose Recordsets.
  5. Name the second CommandButton cmdCloseConnection and set its Caption property to Close Database Connection.
  6. Make the following declarations in the General Declarations section of the form:
    Dim cnNWind As ADODB.Connection
Dim rsCustomers As ADODB.Recordset
Dim rsSuppliers As ADODB.Recordset
  7. Cut and paste the following code in the Click event of cmdOpenRecordsets. Be sure to provide the correct name of your SQL Server:
    Set cnNWind = New ADODB.Connection
cnNWind.CursorLocation = adUseServer

cnNWind.Open "Provider=SQLOLEDB;Data Source=<Name of your SQL Server>;Initial Catalog=Northwind;Trusted_Connection=yes"

Set rsCustomers = New ADODB.Recordset
rsCustomers.Open "Select * from Customers", cnNWind, adOpenForwardOnly, adLockReadOnly<BR/>
'Do While Not rsCustomers.EOF
'rsCustomers.MoveNext
'Loop
Set rsSuppliers = New ADODB.Recordset
rsSuppliers.Open "Select * from Suppliers", cnNWind, adOpenKeyset, adLockReadOnly
  8. Cut and paste the following code in the Click event of cmdCloseConnection:
    cnNWind.Close
Set cnNWind = Nothing
  9. Save the project.
  10. Start an instance of SQL Server Query Analyzer to monitor the connections that are being generated by the ADO code.
  11. Run the project from the Visual Basic IDE, and then click Open Firehose Recordsets.
  12. Run the sp_who statement in SQL Server Query Analyzer to list the active processes and connections. Note that your Visual Basic application has generated two connections, one to manage each of the firehose cursors.
  13. Click Close Database Connection to close and release the ADO connection object and to stop the application.
  14. Uncomment the following lines of code in the Click event of cmdOpenRecordsets:
    'Do While Not rsCustomers.EOF
'rsCustomers.MoveNext
'Loop
  15. Save the project and run it from the Visual Basic IDE.
  16. Click Open Firehose Recordsets, and then switch to the SQL Server Query Analyzer window to monitor the active connections.
  17. When you run sp_who, note that your application has only one active open connection to SQL Server.
This behavior is applicable only to firehose cursors. Other ADO cursor types do not generate additional connections as seen here. Another method to avoid multiple connections from being generated if you must use forward-only, read-only recordsets is to set the CursorLocation of the ADO recordset objects to adUseClient. This results in the creation of a client-side recordset that is not managed by SQL Server, and the connection object is not tied up managing the recordset.
Modification Type:MajorLast Reviewed:1/11/2001
Keywords:kbprb KB258697
©2002 Microsoft Corporation. All rights reserved.