BUG: T-SQL Debugger Is Not Invoked Calling Second Stored Procedure

MORE INFORMATION

Steps to Reproduce Behavior by Using ADO 2.0

1. Create a new Standard EXE project.
2. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library.
3. From the Add-Ins menu, click Add_In Manager and then select the Visual Basic TSQL Debugger Loaded/Unloaded check box.
4. Paste the following code into the Form_Load section of the form. Change the Data Source, Initial Catalog, User ID, and Password in the connection string to match your configuration:

         Dim cn As New ADODB.Connection
         Dim cmd As New ADODB.Command
         Dim cmd2 As New ADODB.Command
         cn.Open "Provider=SQLOLEDB;Data Source=<Your Server>;" & _
                 "Initial Catalog=pubs;User ID=<username>;pwd=<strong password>;"
         cmd.ActiveConnection = cn
         cmd.CommandText = "reptq1"
         cmd.CommandType = adCmdStoredProc
         cmd.Execute  'TSQL Debugger is invoked at this point.
   
         MsgBox "Error Will Occur Next"
   
         cmd2.ActiveConnection = cn
         cmd2.CommandText = "reptq1"
         cmd2.CommandType = adCmdStoredProc
         cmd2.Execute  'TSQL Debugger is not invoked at this point.
   					

5. The error occurs as soon as the Form_Load finishes running.

Steps to Reproduce Behavior by Using ADO 2.1 or Later

1. Create a new Standard EXE project.
2. Add a reference to the Microsoft ActiveX Data Objects Library.
3. From the Add-Ins menu, click Add_In Manager and then select the Visual Basic TSQL Debugger Loaded/Unloaded check box.
4. Paste the following code into the Form_Load section of the form. Change the Data Source, Initial Catalog, User ID, and Password in both connection strings to match your configuration:

   Dim cn As New ADODB.Connection
   Dim cn2 As New ADODB.Connection
   Dim strSQLCmd As String
   Dim cmd As New ADODB.Command
   Dim cmd2 As New ADODB.Command
   Dim adoRs As New ADODB.Recordset
   Dim adoRs2 As New ADODB.Recordset
   Dim prm As ADODB.Parameter
   Dim prm2 As ADODB.Parameter
   
   cn.Open "Provider=SQLOLEDB;Data Source=<<I>Your Server</I>>;" & _
                    "Initial Catalog=pubs;User ID=<username>;pwd=<strong password>;"
   
   cmd.ActiveConnection = cn
   cmd.CommandText = "byroyalty"
   cmd.CommandType = adCmdStoredProc
   
   Set prm = cmd.CreateParameter("Percentage", adInteger, adParamInput, 4, 40)
   cmd.Parameters.Append prm
   
   adoRs.CursorLocation = adUseServer
   adoRs.CursorType = adOpenStatic
   adoRs.LockType = adLockOptimistic
   
   Set adoRs = cmd.Execute
   
   cn2.Open "Provider=SQLOLEDB;Data Source=<<I>Your Server</I>>;" & _
                    "Initial Catalog=pubs;User ID=<username>;pwd=<strong password>;"
   
   cmd2.ActiveConnection = cn   'Use the cn2 connection to resolve the problem.
   cmd2.CommandText = "byroyalty"
   cmd2.CommandType = adCmdStoredProc
   
   Set prm2 = cmd.CreateParameter("Percentage", adInteger, adParamInput, 4, 40)
   cmd2.Parameters.Append prm
   
   adoRs2.CursorLocation = adUseServer
   adoRs2.CursorType = adOpenStatic
   adoRs2.LockType = adLockOptimistic
   
   Set adoRs2 = cmd2.Execute
   					

5. Run the project. The execution of the second stored procedure will either fail silently, or return the error mentioned in the Symptoms section of this article.
6. Change the cmd2.ActiveConnection to use cn2 and run the project. The T-SQL Debugger should run correctly for both stored procedure calls.