June 3, 2010 at 11:54 am
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]
June 3, 2010 at 1:44 pm
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
June 3, 2010 at 2:27 pm
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]
September 22, 2016 at 2:32 pm
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