While using transactions in SQL Server sometimes you get the following errors:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Cause For this error:
There is no transaction in progress but the COMMIT/ROLLBACK TRANSACTION request is issued.
Replication of the scenario:
[sql]
BEGIN TRAN
COMMIT TRAN
COMMIT TRAN
[/sql]
In the above query the first COMMIT TRAN statement has commited the transaction started by BEGIN TRAN, When the second COMMIT TRAN statement is executed the error The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION occurs.
Same is the case for ROLLBACK TRAN in the below query.
[sql]
BEGIN TRAN
ROLLBACK TRAN
ROLLBACK TRAN
[/sql]
Fix For this error:
As a best practice always check for open transactions before issuing COMMIT TRAN or ROLLBACK TRAN statement.
@@TRANCOUNT can be used to find the number of open transactions.
[sql]
IF(@@TRANCOUNT>0)
COMMIT TRAN
[/sql]
[sql]
IF(@@TRANCOUNT>0)
ROLLBACK TRAN
[/sql]
This way the commit/rollback tansaction error can be avoided.
Other Such Scenarios:
[sql]
BEGIN TRAN
COMMIT
COMMIT
[/sql]
[sql]
BEGIN TRAN
COMMIT WORK
COMMIT WORK
[/sql]
[sql]
BEGIN TRAN
ROLLBACK WORK
ROLLBACK WORK
[/sql]
thanks…