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?
Asked
Active
Viewed 2,276 times
1 Answers
14
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.
