Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

How can I query for the server default settings for ARITHABORT, ANSI WARNINGS and ARITHIGNORE?

The server default settings for ARITHABORT are part of the user options bitmask. To retrieve the default options, query the sys.configurations table for the 'user options' setting value and use bit logic to determine the values of each individual setting. ARITHABORT is the 7th bit position so use 64 to determine it's value. The value column in sys.configurations table is a sql_variant type so it's necessary to cast the value into an INT . The current connection's settings can be determined by the value of @@OPTIONS . The default settings for the database can be determined through the sp_dboption stored procedure: sp_dboption 'databaseNameHere', 'arithabort' .

SELECT [ARITHABORT] = CASE CAST(cfg.value AS INT) & 64 --bitwise operation on the 7th position
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END
FROM sys.configurations cfg
WHERE name = 'user options'
-----------------------------------
-- All the user options settings --
-----------------------------------
DECLARE @UserOptionBitValue TABLE 
    (BitValue INT,
     Setting VARCHAR(100),
     SettingDescription VARCHAR(500))
---------------------------------------------------------------------------------
-- User Options definitions 
-- http://msdn.microsoft.com/en-us/library/ms176031.aspx
---------------------------------------------------------------------------------
INSERT @UserOptionBitValue VALUES (1,'DISABLE_DEF_CNST_CHK','Controls interim or deferred constraint checking.')
INSERT @UserOptionBitValue VALUES (2,'IMPLICIT_TRANSACTIONS','For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.')
INSERT @UserOptionBitValue VALUES (4,'CURSOR_CLOSE_ON_COMMIT','Controls behavior of cursors after a commit operation has been performed.')
INSERT @UserOptionBitValue VALUES (8,'ANSI_WARNINGS','Controls truncation and NULL in aggregate warnings.')
INSERT @UserOptionBitValue VALUES (16,'ANSI_PADDING','Controls padding of fixed-length variables.')
INSERT @UserOptionBitValue VALUES (32,'ANSI_NULLS','Controls NULL handling when using equality operators.')
INSERT @UserOptionBitValue VALUES (64,'ARITHABORT','Terminates a query when an overflow or divide-by-zero error occurs during query execution.')
INSERT @UserOptionBitValue VALUES (128,'ARITHIGNORE','Returns NULL when an overflow or divide-by-zero error occurs during a query.')
INSERT @UserOptionBitValue VALUES (256,'QUOTED_IDENTIFIER','Differentiates between single and double quotation marks when evaluating an expression.')
INSERT @UserOptionBitValue VALUES (512,'NOCOUNT','Turns off the message returned at the end of each statement that states how many rows were affected.')
INSERT @UserOptionBitValue VALUES (1024,'ANSI_NULL_DFLT_ON','Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.')
INSERT @UserOptionBitValue VALUES (2048,'ANSI_NULL_DFLT_OFF','Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.')
INSERT @UserOptionBitValue VALUES (4096,'CONCAT_NULL_YIELDS_NULL','Returns NULL when concatenating a NULL value with a string.')
INSERT @UserOptionBitValue VALUES (8192,'NUMERIC_ROUNDABORT','Generates an error when a loss of precision occurs in an expression.')
INSERT @UserOptionBitValue VALUES (16384,'XACT_ABORT','Rolls back a transaction if a Transact-SQL statement raises a run-time error.')
SELECT
    BitValue,
    Setting,
    [DefaultState]= CASE CAST(cfg.value AS INT) & BitValue
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END,
    [CurrentState] = CASE @@OPTIONS & BitValue
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END,
    SettingDescription
    sys.configurations cfg
    CROSS JOIN @UserOptionBitVAlue def
WHERE
    name = 'user options'
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.