Microsoft KB Archive/173391

-

{| The information in this article applies to:
 * width="100%"|
 * ActiveX Data Objects, versions 1.0, 1.5

SYMPTOMS
ActiveX Data Objects (ADO) commands may open multiple connections on SQL Server even though the same connection has been specified for each command.

CAUSE
The connection has not been released by the previously executed command.

RESOLUTION
Use SET COMMAND = NOTHING after executing the command.

The "Steps to Reproduce Behavior" section below also demonstrates how to ensure that commands can use the same connection. Uncomment the three SET mcommandx = Nothing lines.

STATUS
This behavior is by design.

MORE INFORMATION
Active connections on SQL Server may be monitored in SQL Enterprise Manager by bringing up the Current Activity Window, which may be selected from the Server Menu item. The number of connections is listed at the bottom of this window. Each new connection increases the number of idle connections. The refresh button on the toolbar must be pressed to see the changes in the number of connections. Connections may also be monitored using SQL Trace and observing "New Connection" and "Disconnection."

Steps to Reproduce Behavior
This code also includes (commented out) the means to force the connections to be closed.

 Create a project, and under Project References, select Microsoft OLEDB ActiveX Data Objects library. Names may appear slightly different depending on the version of ADO being used.  Create three stored procedures called Test, Test2, and Test3 using isql_w. Each stored procedure should be created by entering and running the following: Create procedure test @result integer output as     select @result =100 Test2 and Test3 should be the same. Change Test to Test2 and Test3, respectively, and change 100 to 200 and 300, respectively.   Add four command buttons to the form with the following labels and captions: Name       Captions -            connect     Establish Connection test       Call Stored Proc Test test2      Call Stored Proc Test2 test3      Call Stored Proc Test3 close_conn Close Connections and Exit   Add the following code to the form: Option Explicit Dim mCommand1 As New Command Dim mCommand2 As New Command Dim mCommand3 As New Command Dim oconnection As New Connection Dim output As Variant Private Sub close_conn_Click oconnection.Close Set oconnection = Nothing Unload Me End Sub Private Sub connect_Click oconnection.Open "DSN=mymachine;UID=sa;PWD=" End Sub Private Sub test_Click Set mCommand1.ActiveConnection = oconnection mCommand1.CommandText = "test" mCommand1.CommandType = adCmdStoredProc Set output = mCommand1.CreateParameter(Type:=adInteger, _ Direction:=adParamOutput) mCommand1.Parameters.Append output mCommand1.Execute MsgBox output 'Set cmd.ActiveConnection = Nothing End Sub Private Sub test2_Click Set mCommand2.ActiveConnection = oconnection mCommand2.CommandText = "test2" mCommand2.CommandType = adCmdStoredProc Set output = mCommand2.CreateParameter(Type:=adInteger, _ Direction:=adParamOutput) mCommand2.Parameters.Append output MsgBox output 'Set cmd.ActiveConnection = Nothing End Sub Private Sub test3_Click Set mCommand3.ActiveConnection = oconnection mCommand3.CommandText = "test3" mCommand3.CommandType = adCmdStoredProc Set output = mCommand3.CreateParameter(Type:=adInteger, _ Direction:=adParamOutput) mCommand3.Parameters.Append output mCommand3.Execute MsgBox output 'Set cmd.ActiveConnection = Nothing End Sub 