Saving and restoring session options settings4
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.
Category: Microsoft SQL Server, Settings, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, Settings, T-SQL
4 thoughts on “Saving and restoring session options settings”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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. 🙂
You’d still have to have the SQLCMD code in each of your scripts. At least as I understand your suggestion 🙂
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’
EXEC dbo.MyScriptLogger ‘MyProcName’ ,’170926′
— 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 🙂
[…] Kenneth Fisher breaks apart the @@OPTIONS bit flags: […]