SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement
Introduction
Section titled “Introduction”Ensure that ROLLBACK TRANSACTION is always preceded by a BEGIN TRANSACTION to avoid runtime errors related to transaction control flow.
Description
Section titled “Description”The proper use of BEGIN TRANSACTION and ROLLBACK TRANSACTION is crucial for managing transaction control flow in SQL Server. A common issue arises when a ROLLBACK TRANSACTION is executed without a preceding BEGIN TRANSACTION , leading to an error because @@TRANCOUNT is zero.
For example:
-- Problematic query exampleROLLBACK TRANSACTION;This statement will cause a runtime error if there isn’t an active transaction when it’s executed. Such errors disrupt normal execution flow, often leading to application-level issues.
-
Causes runtime errors when transaction count is zero.
-
Interrupts the logical unit of work if improperly handled.
How to fix
Section titled “How to fix”Ensure proper match between ROLLBACK and BEGIN TRANSACTION statements to prevent runtime errors.
Follow these steps to address the issue:
1.Identify all ROLLBACK TRANSACTION statements in your SQL code.
2.Verify that each ROLLBACK TRANSACTION statement is preceded by a corresponding BEGIN TRANSACTION . This ensures there is an active transaction when the rollback is executed.
3.If a ROLLBACK TRANSACTION lacks a preceding BEGIN TRANSACTION , remove it or modify the logic to include initiating a transaction appropriately.
4.Test the corrected SQL statements to ensure there are no runtime errors due to mismatched transaction control statements.
For example:
-- Corrected query exampleBEGIN TRANSACTION;-- ... SQL operations ...ROLLBACK TRANSACTION;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”5 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”BEGIN TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
Example Test SQL
Section titled “Example Test SQL”ROLLBACK
BEGIN TRANSACTION
ROLLBACK
COMMIT
ROLLBACK
BEGIN TRANSACTIONROLLBACK
ROLLBACKAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. | 1 | 0 |
| 2 | SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. | 9 | 0 |
| 3 | SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement. | 14 | 0 |