INFO: Opening Multiple ForwardOnly ADO Recordsets with SQL Server Spawns Additional Connections

• Microsoft SQL Server versions 6.5, 7.0
• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

When you try to open more than one ForwardOnly ADO recordset on a single Connection object at a time, only the first ADO recordset is actually opened on the Connection object. New, separate connections are created for subsequent ForwardOnly recordsets that you try to open on the Connection object.

Additional connections are opened because SQL Server can only open one ForwardOnly cursor at a time on a connection. SQL Server can only process one active statement at a time per connection.

MORE INFORMATION

The following sample demonstrates that additional connections are opened when more than one ForwardOnly recordset is opened on a single Connection object:

1. In Visual Basic, create a new Standard EXE project. Form1 is created by default.



2. Add a Project Reference to Microsoft ActiveX Data Objects Library.



3. Add a command button to Form1. Command1 is added by default.



4. Paste the following code into the General Declarations section of Form1. Replace sql_server_name with the name of your SQL Server:




Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Private Sub Command1_Click()

 Set cn = New ADODB.Connection
 Set rs = New ADODB.Recordset
 Set rs2 = New ADODB.Recordset

' Open a single Connection object to SQL Server
 With cn
    .Provider = "SQLOLEDB"
    .ConnectionString = "Data Source=sql_server_name;User ID=sa;Password=;"
   'The first connection to SQL Server is opened
    .Open
    .DefaultDatabase = "Pubs"
 End With

' Open the first ForwardOnly recordset.
 rs.Open "select * from Authors", cn

' Open the second ForwardOnly recordset. 
'  Although cn is specified, actually a new connection is opened
 rs2.Open "select * from Authors", cn

' Release objects
 rs.Close
 Set rs = Nothing

'The second connection to SQL is closed
 rs2.Close
 Set rs2 = Nothing

'The first connection to SQL is closed
 cn.Close
 Set cn = Nothing

End Sub 

5. In SQL Server, start the SQL Performance Monitor (SQL 6.5) or System Performance Monitor (SQL 7). Add the Counter "User Connections" to the chart. In System Performance Monitor (SQL 7), User Connections are in the object SQLServer:General Statistics.



6. In Visual Basic, step through the code by repeatedly pressing the F8 key. As you step through, watch the number of User Connections in Performance Monitor. Opening cn opens one connection to the server; opening rs2 opens a second connection to the server. Closing rs2 releases a connection; closing cn and setting cn to Nothing releases the first connection.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ammar Abuthuraya, Microsoft Corporation

REFERENCES

Additional query words:

Keywords : kbADO kbDatabase kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2; winnt:6.5,7.0
Platform : WINDOWS winnt
Issue type : kbinfo