FAQ

I keep receiving the following warning during bulk updates through stored procedures called from BizTalk. Please help resolve this issue.

The warning reads as "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." And "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."

Description

The error occurs most often when stored procedures are executed in chained mode. In chained mode, if there is no explicit begin transaction statement, an implicit begin transaction is issued. A begin transaction statement, whether implicit or explicit, increments the session transaction count by 1.

In chained mode, the following statements cause an implicit begin transaction to be issued: select, update, delete, insert, and fetch.

Each begin transaction statement in a stored procedure, whether implicit or explicit, must be balanced by a commit transaction statement, which decrements the session transaction count by 1. Otherwise, the transaction count remains higher when the stored procedure exits than it was at the beginning of execution.

In addition, Error occurs when you are using nested procedures, and procedures at each level of nesting include begin, commit, and rollback transaction statements. If a procedure at a lower nest level opens a transaction and one of the called procedures issues a rollback transaction, Error occurs when you exit the nested procedure.