Article ID: 810100 - View products that this article applies to.
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)
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.
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:
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
Steps to Reproduce the Behavior
For more information, visit the following MSDN Web site:
Microsoft ADO and SQL Server Developer's Guide
Article ID: 810100 - Last Review: November 13, 2006 - Revision: 3.1
Contact us for more help
Connect with Answer Desk for expert help.