Microsoft KB Archive/235282

= INFO: SQL Server Spawns Additional Connections When You Open Multiple ForwardOnly ADO Recordsets =

Article ID: 235282

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * 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
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q235282



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= and 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= ;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  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. 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.</ol>

<div class="references_section">