How to make SET XACT_ABORT ON rollback the transaction?

Question

Based on the Books Online documentation of SET XACT_ABORT ON, i get the impression that if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back:

Remarks

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Testing this in SQL Server 2008 R2:

SET XACT_ABORT ON; BEGIN TRANSACTION; PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50)) DROP TABLE QuertyAsdf PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))

Gives the output:

TranCount befor an error = 1 Msg 3701, Level 11, State 5, Line 6 Cannot drop the table 'QwertyAsdf', because it does not exist or you do not have permission. TranCount after an error = 1

i was also under the impression that SET XACT_ABORT ON terminates the batch if there's an error:

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.

That sounds handy. How can i make it do that too?

"when XACT_ABORT is ON, not all errors terminate the batch ... Compilation errors (which normally terminate the scope) do not terminate the batch." — Martin Smith
– Martin Smith, Commented Jul 24, 2012 at 6:33
@MartinSmith Presumably this is not a compilation error, as it passes compilation and begins executing the batch — Ian Boyd
– Ian Boyd, Commented Jul 25, 2012 at 19:55
Generally speaking statements that reference non existent objects are subject to deferred compile. Not sure if this is the case for DDL statements. — Martin Smith
– Martin Smith, Commented Jul 25, 2012 at 20:34

Branko Dimitrijevic · Accepted Answer · 2019-02-22 13:55:15Z

The SQL Server only rollback transactions when Severity level greater or equals 16.

See example:

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' whenIDENTITY_INSERT is set to OFF.

Test on SQL Server 2008 R2

SET XACT_ABORT ON; BEGIN TRANSACTION; PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50)) insert into ORC_ORCAMENTO (ORCID, ORCNOME, ORCATIVO) VALUES (1, 'TESTE_ALEXP', 0); PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))

Returns

TranCount befor an error = 1 Msg 544, Level 16, State 1, Line 5 Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' when IDENTITY_INSERT is set to OFF. TranCount after an error = 0

See Microsoft Error Message Levels on

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors

Artur Udod · Accepted Answer · 2012-11-14 17:36:57Z

When you use xact abort on, in the try catch statement, you can manually raise an error to make the transaction roll back.

set xact_abort on; begin try ...dml statements here.... if conditions here... raiseerror(....); end try begin catch end catch

Collectives™ on Stack Overflow

How to make SET XACT_ABORT ON rollback the transaction?

Remarks

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Remarks

2 Answers 2

Comments

Comments

Linked

Related