Menu
What is the scope of a SET XACT_ABORT statement in SQL Server 2005? i.e.:begin-end block, procedure or trigger, connection, database, server?
310
SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)
Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
Technet Using Options in SQL Server hints that all SET options are scoped at connection or batch level.
MSDN SET Statements adds details:
If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.
It's also possible to enable XACT_ABORT by default for all users via user options:
EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE
It can also be enforced for selected users only via custom logon trigger.
See also important details on XACT_ABORT behaviour.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
