Saving and restoring session options settings

4

September 25, 2017 by Kenneth Fisher

There are a fair number of options settings. ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, etc. Each session has its own set of configurations. They are initially set based on the user settings system configuration, then the various connection programs (SSMS for example) can override that, then the various SET commands can override that.

Now personally I prefer to keep my settings to the default to eliminate confusion but they do get changed occasionally. Just as a for example, when you generate a script from SSMS it typically includes a bunch of SET ON and SET OFF commands. And if you turn on a setting that was already on, then turn it off and the end, well, your setting has changed unexpectedly. My original intent for this post was to create a stored procedure that would let you save the current settings and restore them. Unfortunately, I ran into a scope problem. I can find the current settings: @@options. I can break down the integer value using a script from here (just in case the post should disappear before this one does here is the code from the article)

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Then I modified that slightly to restore the options.

DECLARE @options int

-- Set @options to the old value of @@options.

-- https://technet.microsoft.com/en-us/library/ms175103(v=sql.105).aspx
--IF SERVERPROPERTY('ProductVersion') < '10.6'
--	IF ( (1 & @options) = 1)
--		SET DISABLE_DEF_CNST_CHK ON
--	ELSE
--		SET DISABLE_DEF_CNST_CHK OFF
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql
IF ( (2 & @options) = 2)
	SET IMPLICIT_TRANSACTIONS ON
ELSE
	SET IMPLICIT_TRANSACTIONS OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-cursor-close-on-commit-transact-sql
IF ( (4 & @options) = 4) 
	SET CURSOR_CLOSE_ON_COMMIT ON
ELSE
	SET CURSOR_CLOSE_ON_COMMIT OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql
IF ( (8 & @options) = 8)
	SET ANSI_WARNINGS ON
ELSE
	SET ANSI_WARNINGS OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql
IF ( (16 & @options) = 16)
	SET ANSI_PADDING ON
ELSE
	SET ANSI_PADDING OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql
IF ( (32 & @options) = 32)
	SET ANSI_NULLS ON
ELSE
	SET ANSI_NULLS OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql
IF ( (64 & @options) = 64)
	SET ARITHABORT ON
ELSE
	SET ARITHABORT OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithignore-transact-sql
IF ( (128 & @options) = 128)
	SET ARITHIGNORE ON
ELSE
	SET ARITHIGNORE OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql
IF ( (256 & @options) = 256) 
	SET QUOTED_IDENTIFIER ON
ELSE
	SET QUOTED_IDENTIFIER OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql
IF ( (512 & @options) = 512)
	SET NOCOUNT ON
ELSE
	SET NOCOUNT OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql
IF ( (1024 & @options) = 1024)
	SET ANSI_NULL_DFLT_ON ON
ELSE
	SET ANSI_NULL_DFLT_ON OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-off-transact-sql
IF ( (2048 & @options) = 2048) 
	SET ANSI_NULL_DFLT_OFF ON
ELSE
	SET ANSI_NULL_DFLT_OFF OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql
IF ( (4096 & @options) = 4096) 
	SET CONCAT_NULL_YIELDS_NULL ON
ELSE
	SET CONCAT_NULL_YIELDS_NULL OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-numeric-roundabort-transact-sql
IF ( (8192 & @options) = 8192) 
	SET NUMERIC_ROUNDABORT ON
ELSE
	SET NUMERIC_ROUNDABORT OFF

-- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql
IF ( (16384 & @options) = 16384) 
	SET XACT_ABORT ON
ELSE
	SET XACT_ABORT OFF

Things were going great until I put the code into a stored procedure. And then as I said above I discovered a scope issue. The scope of the set commands is local. Meaning that while they changed the options, they only changed them within the stored procedure (and presumably any code I called from within the stored procedure). As soon as I came back out of the stored procedure the options were back to where they were.

So basically this post is here to help people (well, really me) find the list of values for the various options. Also in case anyone wants/needs the restore code.

4 thoughts on “Saving and restoring session options settings

  1. Chad Estes says:

    Seems like you could use SQLCMD mode to output the commands you need to restore your defaults using a stored proc, then run that output script within your code. Kinda of a kludge but it would make it slightly easier. 🙂

  2. Kris says:

    I tackle this in a different way – dunno if it is suitable for other folk though

    I store source code for each SProc, View, Trigger, Function etc. in a separate file (and , in turn, they are stored in a Revision Control repository)

    At the top of each file is an EXEC of a Procedure which “logs” the fact that that script was run.

    PRINT ‘Create Procedure MyProcName’
    GO
    EXEC dbo.MyScriptLogger ‘MyProcName’ ,’170926′
    GO

    — some code to create a STUB if object does not exist

    ALTER PROCEDURE dbo.MyProcName

    MyScriptLogger takes a Name and Version. I just use today’s date for the version, its not terribly critical. The version is wide enough to accomodate a few extra characters – “a”, “b”, “c” or “DEL” if the script is changed such that the object is deprecated

    MyScriptLogger checks that all the Session Option Settings are “as expected” – if not it outputs a warning, and a suitable “Run this” command to rectify it.

    It also checks if the script is running IN a Transaction – that happens sometimes when I forget to Commit/Rollback some “Not sure about this” job. Of course if I create the new Object inside a transaction and then, for some reason, the transaction gets rolled back I’ll lose the new version of my script … and then probably scratch my head for some considerable time!

    EXEC dbo.MyScriptLogger ‘MyProcName’

    (without version parameter) will produce a time/date list of execution history – showing which versions etc. It will also “complain” if the Log Time is LATER than the Modify Time for the object – i.e. the script was logged, but the ALTER failed for some reason.

    I use the date/time that a script was run, and the version number, as a guide when rolling out changes from DEV to QA. Subsequent changes to Production use the scripts that were run on QA, so should not need the same “comparison” against DEV / QA, but that option exists of course 🙂

    The formatting of the EXEC dbo.MyScriptLogger command has the comma-separator right-aligned, which makes it easier to highlight the

    EXEC dbo.MyScriptLogger ‘MyProcName’

    part, to get a history-list, avoiding including the comma (and version parameter) too.

    I have some additional, optional, parameters to MyScriptLogger:

    @LastRun = 1 – includes a history list of when the SProc was last run (from logs I have of SProc executions)

    @TODO = ‘xxx’ – add a ToDo comment to my Bug/Feature Tracking system – its a tacky way of creating an entry for a “Couldn’t be bothered to fix this just now” issue … 🙂

    MyScriptLogger also alerts if the object name does not conform to our naming standards.

    It also checks if there is an object of the same name with “_HOLD” suffix. We rename objects to “%_HOLD” when doing long term coverage tests (our APP automatically renames them back on first-execution if it hits a “Object not found” error). Thus important not to create a new FooBar if FooBar_HOLD exists 🙂

  3. […] Kenneth Fisher breaks apart the @@OPTIONS bit flags: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: