SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement

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:

1-- Problematic query example
2ROLLBACK 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.

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:

1-- Corrected query example
2BEGIN TRANSACTION;
3-- ... SQL operations ...
4ROLLBACK TRANSACTION;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

BEGIN TRANSACTION (Transact-SQL)

ROLLBACK TRANSACTION (Transact-SQL)

Other Resources