PRB: Multiple Connections Opened Using ADO

• 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.

1. 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.

2. 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.

3. 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
   
   

4. 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
   
   

REFERENCES

For additional information on ADO commands download the ADO Help file from the Internet at http://microsoft.com/ado/.

Keywords          : adoall adoengdb
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbprb