BUG: "No Corresponding BEGIN TRANSACTION" Error When COMMIT TRANSACTION Is Run

Article translations Article translations
Article ID: 810100 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

When you call a recursive SQL Server stored procedure from an ADO application by using ODBC CALL syntax in a TRANSACTION, you may receive the following error message when COMMIT TRANSACTION is run:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (Microsoft OLE DB Provider for SQL Server)

CAUSE

ADO causes Microsoft SQL Server to run the stored procedure on two occasions. First, it runs the procedure with SET FMTONLY set to ON. When SET FMT ONLY is set to ON, SQL Server always exceeds the maximum nesting level (32) irrespective of the parameter that is passed. Therefore, SQL Server aborts the batch, and rolls back the TRANSACTION. Because the SQL Server internally runs this method, no error is reported. SQL Server runs the stored procedure again. This time, depending on the parameter that is passed, the execution of the stored procedure occurs.

When this second execution of the stored procedure exceeds the 32-level limit, the correct error message is returned. When the correct execution of the stored procedure does not exceed the nesting level maximum, you receive the error message that is described in the "Symptoms" section. This occurs when COMMIT TRANSACTION is run. It is run because SQL Server rolled back the TRANSACTION when the maximum nesting was exceeded, and SQL Server aborted the batch.

RESOLUTION

To work around this problem, do not call the stored procedure by using ODBC Call syntax. You can call the stored procedure in may ways by using ADO. One of the methods is to call a stored procedure by using a command object. The following is an example:
  ' Command to execute the stored procedure
   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command
   With cmd
      ' Specify the command type as Stored Procedure
      .CommandType = adCmdStoredProc
      ' Specify the Stored Procedure to call
      .CommandText = "RecursionTest"
      
      ' Specify the Connection to be used
      Set .ActiveConnection = cn
      
      ' Set up new parameter for the stored procedure
      Dim prm As Parameter
      Set prm = cmd.CreateParameter("@Param", adInteger, adParamInput, , -35)
      .Parameters.Append prm
      
      ' Execute the Stored Procedure
      .Execute
   End With
   
   Set cmd = Nothing

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open SQL Query Analyzer.
  2. In the Northwind sample database, run the following SQL script to create a recursive stored procedure:
    CREATE PROCEDURE RecursionTest @Param Int As 
    Set @Param = @Param + 1 
    If @Param < 3 
          Exec RecursionTest @Param 
  3. Create a new Standard EXE project in Visual Basic 6.0. By default, Form1 is created.
  4. On the Project menu, click References.
  5. In the References list, double-click Microsoft ActiveX Data Objects 2.7 Library, and then click OK.
  6. In the toolbox, double-click CommandButton to add the CommandButton to the Form. By default, Command1 is created.
  7. Double-click Button1 to add code for the click event of Command1.
  8. Replace the existing code in the Command1_Click event with the following code:
    Private Sub Command1_Click()
       
       ' Connect to SQL Server
       Dim cn As ADODB.Connection
       Set cn = New ADODB.Connection
       cn.Open "Provider=SQLOLEDB; Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"
       
       ' Begin a Transaction
       cn.Execute "BEGIN TRANSACTION", , adExecuteNoRecords
       
       ' Create a Command to execute Stored Procedure
       Dim cmd As ADODB.Command
       Set cmd = New ADODB.Command
       With cmd
          strSQL = "{ call RecursionTest (?)}"
          .CommandType = adCmdText
          .CommandText = strSQL
          Set .ActiveConnection = cn
          .Prepared = True
          .Parameters(0) = -3
          
          ' Execute the Stored Procedure
          .Execute
       End With
       
       Set cmd = Nothing
       
       ' Commit the Transaction
       cn.Execute "COMMIT TRANSACTION", , adExecuteNoRecords
       cn.Close
       Set cn = Nothing
    End Sub
    
  9. On the Run menu, click Start to run the application.
  10. Click Command1.

REFERENCES

For more information, visit the following MSDN Web site:
Microsoft ADO and SQL Server Developer's Guide
http://www.microsoft.com/downloads/details.aspx?familyid=87468baa-af9a-4e2b-a71b-7f9e51882f25&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en

Properties

Article ID: 810100 - Last Review: November 13, 2006 - Revision: 3.1
APPLIES TO
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 6.0 Professional Edition
Keywords: 
kbstoredproc kbsqlprog kbdatabase kbclient kberrmsg kbbug KB810100

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com