How can I find out whether XACT_ABORT is ON or OFF at instance level?

  • How can I find out whether XACT_ABORT is ON or OFF at instance level?

    I tried using sp_configure and looking online, but, apparently, this is not as easy as it sounds.

    Any help would be appreciated.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • You can find out if it's set by running select @@options

    then reference this link: http://www.mssqltips.com/tip.asp?tip=1415

    The link has a "readable result" query for all of the user options.

    Cindy

  • Thank you, that worked!

    Why so complicated to get a simple setting back?? :w00t:

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Marios Philippopoulos (6/3/2010)


    How can I find out whether XACT_ABORT is ON or OFF at instance level?

    I tried using sp_configure and looking online, but, apparently, this is not as easy as it sounds.

    Any help would be appreciated.

    I actually posted this in another thread thinking I was posting here....so now I post here as originally intended...

    The answer lies in performing a simple BITWISE AND operation against @@OPTIONS, unless you know all these values yourself and can do them in your head (I CANNOT), here is a script to find all of the possible options and whether they are ON or OFF.

    Enjoy...

    --ONLY USE THESE FOR TESTING PURPOSES OTHERWISE YOU WILL GET THE SERVER/SESSION VALUES.

    --SET XACT_ABORT OFF

    --SET NUMERIC_ROUNDABORT OFF

    --SET CONCAT_NULL_YIELDS_NULL OFF

    --SET ANSI_NULL_DFLT_OFF OFF

    --SET ANSI_NULL_DFLT_ON OFF

    --SET NOCOUNT OFF

    --SET QUOTED_IDENTIFIER OFF

    --SET ARITHIGNORE OFF

    --SET ARITHABORT OFF

    --SET ANSI_NULLS OFF

    --SET ANSI_PADDING OFF

    --SET ANSI_WARNINGS OFF

    --SET CURSOR_CLOSE_ON_COMMIT OFF

    --SET IMPLICIT_TRANSACTIONS OFF

    ----SET DISABLE_DEF_CNST_CHK OFF --Obsolete in 2008 and greater

    DECLARE @OPTIONS INT

    DECLARE @OPTRESULTS VARCHAR(MAX)

    SET @OPTIONS=(SELECT @@OPTIONS)

    SET @OPTRESULTS = '@@OPTIONS= '+ CAST(@OPTIONS AS VARCHAR(20))

    IF (@OPTIONS & 16384)=16384

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'XACT_ABORT ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'XACT_ABORT OFF'

    IF (@OPTIONS & 8192)=8192

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NUMERIC_ROUNDABORT ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NUMERIC_ROUNDABORT OFF'

    IF (@OPTIONS & 4096)=4096

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CONCAT_NULL_YIELDS_NULL ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CONCAT_NULL_YIELDS_NULL OFF'

    IF (@OPTIONS & 2048)=2048

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_OFF ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_OFF OFF'

    IF (@OPTIONS & 1024)=1024

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_ON ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULL_DFLT_ON OFF'

    IF (@OPTIONS & 512)=512

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NOCOUNT ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'NOCOUNT OFF'

    IF (@OPTIONS & 256)=256

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'QUOTED_IDENTIFIER ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'QUOTED_IDENTIFIER OFF'

    IF (@OPTIONS & 128)=128

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHIGNORE ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARTHIGNORE OFF'

    IF (@OPTIONS & 64)=64

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHABORT ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ARITHABORT OFF'

    IF (@OPTIONS & 32)=32

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULLS ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_NULLS OFF'

    IF (@OPTIONS & 16)=16

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_PADDING ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_PADDING OFF'

    IF (@OPTIONS & 8)=8

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_WARNING ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'ANSI_WARNING OFF'

    IF (@OPTIONS & 4)=4

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CURSOR_CLOSE_ON_COMMIT ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'CURSOR_CLOSE_ON_COMMIT OFF'

    IF (@OPTIONS & 2)=2

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'IMPLICIT_TRANSACTIONS ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'IMPLICIT_TRANSACTIONS OFF'

    IF (@OPTIONS & 1)=1

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'DISABLE_DEF_CNST_CHK ON'

    ELSE

    SET @OPTRESULTS= @OPTRESULTS + CHAR(13) + 'DISABLE_DEF_CNST_CHK OFF'

    PRINT @OPTRESULTS

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply