The information in this article applies to:
- ActiveX Data Objects, versions 1.0, 1.5
ActiveX Data Objects (ADO) commands may open multiple connections on SQL Server even though the same connection has been specified for each command.
The connection has not been released by the previously executed command.
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.
This behavior is by design.
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
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:
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:
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
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