Florian (RE**********************@gmx.net) writes:
I need to set multiple values for some SQL statements, for example
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
in a .sql file, but would like to reset them to their previous settings
again for other SQL statements. I didn't find anything in books online. I
thought just calling "set" was enough, but apparently not ....
First, since these are all (but ARITHABORT) the default settings for ODBC
and OLE DB, you should think twice before changing them back.
When you change a SET option, the setting will be reverted to its previous
state when you exit the scope where the SET command was executed. Thus,
if you SET it directly on connection level, the SET command will never
revert. But if you put all commands, SET commands and the commands you
want them to be in effect for, in a stored procedure, or in EXEC:
EXEC('SET NUMERIC ROUNDABORT OFF;
SET ANSI_PADDING, ... ON;
-- other commands')
you achieve what you want. Well almost. You might need a nested
EXEC() statement for SET ANSI_NULLS to have effect.
However, since these are the settings for indexed views and indexed
computed columns, I suspect are creating views or stored procedures,
so this is probably getting messy.
There is still another way, but it is not less messy. You can retrieve
the current settings in two ways: DBCC USEROPTIONS and the global variable
@@options. The latter is a bit mask, please see Books Online for details.
You can catch DBCC USEROPTIONS to a table, but an option will only be
there if set.
A cleaner solution may be to have a client program that controls your
script.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp