sql-serversql-server-2005scopesqltransactionxact-abort

What is the scope of XACT_ABORT


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?


Solution

  • 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.