Q258697: INFO: Single SQL Connection Supports Only One Active Firehose

Article: Q258697
Product(s): Microsoft Visual Basic for Windows
Version(s): 2.1,2.1 SP1,2.1 SP2,2.5,5.0,6.0,7.0
Operating System(s): 
Keyword(s): kbADO210 kbVBp500 kbVBp600 kbSQLServ700 kbGrpDSVBDB kbADO210sp2 kbMDAC210 kbMDAC210SP2
Last Modified: 11-JAN-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0 
- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
- Microsoft SQL Server version 7.0 
- ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5 
-------------------------------------------------------------------------------

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.

Additional query words:

======================================================================
Keywords          : kbADO210 kbVBp500 kbVBp600 kbSQLServ700 kbGrpDSVBDB kbADO210sp2 kbMDAC210 kbMDAC210SP2 kbMDAC250 kbADO250 
Technology        : kbVBSearch kbSQLServSearch kbAudDeveloper kbADOsearch kbADO210 kbADO210sp1 kbADO210sp2 kbADO250 kbZNotKeyword6 kbSQLServ700 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600
Version           : :2.1,2.1 SP1,2.1 SP2,2.5,5.0,6.0,7.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.