Explicitly declare a Database Transaction

When SET XACT_ABORT is ON, if a running error occurs when a Transact-SQL statement is executed, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, only the wrong Transact-SQL statement is generated and the transaction will continue to be processed. If the error is serious, the entire transaction may be rolled back even if SET XACT_ABORT is OFF.

Compilation errors (such as syntax errors) are not affected by SET XACT_ABORT.

For most ole db providers (including SQL Server), XACT_ABORT In the implicit or display transaction data modification statement must be set to ON. 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.

 

 

Set xact_abort on

Begin tran

....

SQL statement

...

Commit tran