DOCUMENT:Q258697 11-JAN-2001 [vbwin] TITLE :INFO: Single SQL Connection Supports Only One Active Firehose PRODUCT :Microsoft Visual Basic for Windows PROD/VER::2.1,2.1 SP1,2.1 SP2,2.5,5.0,6.0,7.0 OPER/SYS: KEYWORDS:kbADO210 kbVBp500 kbVBp600 kbSQLServ700 kbGrpDSVBDB kbADO210sp2 kbMDAC210 kbMDAC210SP2 ====================================================================== ------------------------------------------------------------------------------- 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=;Initial Catalog=Northwind;Trusted_Connection=yes" Set rsCustomers = New ADODB.Recordset rsCustomers.Open "Select * from Customers", cnNWind, adOpenForwardOnly, adLockReadOnly
'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 2001.