INFO: SQL Server Spawns Additional Connections When You Open Multiple ForwardOnly ADO Recordsets (235282)

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.

RESOLUTION

Using any one of the following methods will allow the same connection to be used to open multiple recordsets:

Use a different cursor type, such as adOpenDynamic, adOpenKeyset, or adOpenStatic.
Use a client side cursor instead of a server side cursor.
Close each recordset prior to opening another on the same connection.

All three methods are commented into the sample code below.

STATUS

This behavior is by design

MORE INFORMATION

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

In Visual Basic, create a new Standard EXE project. Form1 is created by default.
Add a Project Reference to Microsoft ActiveX Data Objects Library.
Add a command button to Form1. Command1 is added by default.

Paste the following code into the General Declarations section of Form1. Replace sql_server_name with the name of your SQL Server:
Note You must change User ID=<username> and Password=<strong password> to the appropriate username and password before you run this code. Make sure that the changed User ID has the appropriate permissions to perform the required operations on the specified database.

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=<username>;Password=<strong password>;"
   'The first connection to SQL Server is opened
    .Open
    .DefaultDatabase = "Pubs"
 End With
'cn.CursorLocation = adUseClient

' Open the first ForwardOnly recordset.
 rs.Open "select * from Authors", cn 'Specify a different cursor type here.
'rs.Close

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


' 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