Microsoft KB Archive/258697

= PRB: Single SQL Server Connection Can Support Only One Active Firehose Cursor =

Article ID: 258697

Article Last Modified on 1/11/2001

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 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.



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.  Open a new Standard EXE project in Visual Basic. Form1 is created by default. Set a Project reference to the Microsoft ActiveX Data Objects 2.X Library. Drag and drop two CommandButtons on to Form1. Name the first CommandButton cmdOpenRecordsets and set its Caption property to Open Firehose Recordsets. Name the second CommandButton cmdCloseConnection and set its Caption property to Close Database Connection.  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 </li>  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 &quot;Provider=SQLOLEDB;Data Source=<Name of your SQL Server>;Initial Catalog=Northwind;Trusted_Connection=yes&quot;

Set rsCustomers = New ADODB.Recordset rsCustomers.Open &quot;Select * from Customers&quot;, cnNWind, adOpenForwardOnly, adLockReadOnly<BR/> 'Do While Not rsCustomers.EOF 'rsCustomers.MoveNext 'Loop Set rsSuppliers = New ADODB.Recordset rsSuppliers.Open &quot;Select * from Suppliers&quot;, cnNWind, adOpenKeyset, adLockReadOnly </li>  Cut and paste the following code in the Click event of cmdCloseConnection: cnNWind.Close Set cnNWind = Nothing </li> Save the project.</li> Start an instance of SQL Server Query Analyzer to monitor the connections that are being generated by the ADO code.</li> Run the project from the Visual Basic IDE, and then click Open Firehose Recordsets.</li> 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.</li> Click Close Database Connection to close and release the ADO connection object and to stop the application.</li>  Uncomment the following lines of code in the Click event of cmdOpenRecordsets: 'Do While Not rsCustomers.EOF 'rsCustomers.MoveNext 'Loop </li> Save the project and run it from the Visual Basic IDE.</li> Click Open Firehose Recordsets, and then switch to the SQL Server Query Analyzer window to monitor the active connections.</li> When you run sp_who, note that your application has only one active open connection to SQL Server.</li></ol>

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.

Keywords: kbprb KB258697

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.