Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q191513

↑ Back to the top


Symptoms

If multiple stored procedures are executed when using ADO 2.0, the T-SQL Debugger automatically starts for the first stored procedure, but does not automatically start upon executing the second or subsequent stored procedures. The following error message displays:
The query could not be debugged due to a problem coordinating events with the server. Check the server and client log to find the exact cause, fix the problem and try again.
This problem no longer occurs in ADO 2.1 and later when calling simple stored procedures like the pubs..reptq1 procedure that is called in the example code below. However, if you are calling multiple stored procedures that accept parameters, using a single ADO Connection, the T-SQL Debugger does not start automatically when executing the second or subsequent stored procedures. You will either receive the error described above, or it will fail silently.

↑ Back to the top


Resolution

In ADO 2.1 and later, to temporarily work around this while debugging, execute each store procedure on a separate connection. This workaround is illustrated in "Steps to Reproduce Behavior Using ADO 2.1" that follows.

↑ Back to the top


Status

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

↑ Back to the top


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.

↑ Back to the top


Keywords: kbbug, kbdebug, kbpending, kbstoredproc, KB191513

↑ Back to the top

Article Info
Article ID : 191513
Revision : 4
Created on : 11/5/2003
Published on : 11/5/2003
Exists online : False
Views : 422