Microsoft KB Archive/173391

From BetaArchive Wiki



PRB: Multiple Connections Opened Using ADO

Last reviewed: September 3, 1997
Article ID: Q173391



The information in this article applies to:

  • 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



================================================================================



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.