Set xact_abort usage [go]

Set xact_abort
When a running error occurs in a Transact-SQL statement®SQL Server™Whether to automatically roll back the current transaction.

Syntax

Set xact_abort {on | off}

Note

When set xact_abort is on, if a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only the wrong Transact-SQL statement is returned, and the transaction will continue to be processed. Compilation errors (such as syntax errors) are not affected by set xact_abort.

For most ole db providers (including SQL Server), xact_abort must be set to on for data modification statements in implicit or explicit transactions. The only case where this option is not required is when the Provider supports nested transactions. For more information, see distributed queries and distributed transactions.

Set xact_abort is set during execution or running, rather than during analysis.

Example

In the following example, a foreign key violation error occurs in transactions that contain other Transact-SQL statements. Errors are generated in the first statement set, but other statements are successfully executed and the transaction is successfully committed. In the second statement set, set xact_abort to on. This causes a statement error to terminate the batch processing and roll back the transaction.

 

Create Table T1 (A int primary key)
Create Table T2 (A int references T1 ())
Go
Insert into T1 values (1)
Insert into T1 values (3)
Insert into T1 values (4)
Insert into T1 values (6)
Go
Set xact_abort off
Go
Begin tran
Insert into T2 values (1)
Insert into T2 values (2)/* foreign key Error */
Insert into T2 values (3)
Commit tran
Go

Set xact_abort on
Go

Begin tran
Insert into T2 values (4)
Insert into T2 values (5)/* foreign key Error */
Insert into T2 values (6)
Commit tran
Go

/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back,
Xact_abort was off and rest of transaction
Succeeded.
Key 5 insert error with xact_abort on caused
All of the second transaction to roll back .*/

Select *
From T2
Go

Drop table T2
Drop table T1
Go

Set xact_abort usage [go]