setting and restoring multiple connection options

[Florian]

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_IDENTIF
IER,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 ....
Thanks.

[Erland Sommarskog]

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

[Florian]

> First, since these are all (but ARITHABORT) the default settings for ODBC

and OLE DB, you should think twice before changing them back.
Oh, well in this case I don't have to worry about that too much then. How
would I know what the default settings are?
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.
I'm not sure if I got the NETSTED EXEC but I might give it a try, though it
does sound like it makes the whole code less readable. I'll play around with
it.
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.
Exactly - I'm creating indexed views, indexes and stored procedures :-)
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.

Ugh.

Thanks for your input!

[Dan Guzman]

> > First, since these are all (but ARITHABORT) the default settings for
ODBC

and OLE DB, you should think twice before changing them back.
Oh, well in this case I don't have to worry about that too much then. How
would I know what the default settings are?

From the SQL Server 2000 Books Online:

<Excerpt href="createdb.chm::/cm_8_des_05_1ng3.htm">
Six of the seven SET option settings required for indexes on computed
columns and views are the default settings for the OLE DB Provider for SQL
Server and the SQL Server ODBC driver. These settings are:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

These settings also enforce the rules of the SQL-92 standard and are the
recommended settings for SQL Server. Because DTS, replication, and bulk copy
operations in SQL Server 2000 use OLE DB or ODBC, these options are also
automatically set for these operations. Some of the SQL Server utilities set
one or more of the ANSI settings to OFF to maintain compatibility with
earlier versions of the utilities.

SET ARITHABORT ON is the one option that is not automatically set for
connections using the OLE DB Provider for SQL Server or the SQL Server ODBC
driver. OLE DB and ODBC connections do not specify an ARITHABORT setting, so
connections default to the server default, which is ARITHABORT OFF. This
server default is controlled by the user options server option. The user
options bit that equates to 64 should be set for any server on which you
implement indexes on views or computed columns. For more information about
how to set this option, see user options Option.

</Excerpt>

You can also set ARITHABORT at the database level with ALTER DATABASE. For
example:

ALTER DATABASE MyDatabase
SET ARITHABORT ON

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Florian" <RE**********************@gmx.net> wrote in message
news:gw*******************@newsread2.news.pas.eart hlink.net...

First, since these are all (but ARITHABORT) the default settings for ODBC and OLE DB, you should think twice before changing them back.

Oh, well in this case I don't have to worry about that too much then. How
would I know what the default settings are?

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.

I'm not sure if I got the NETSTED EXEC but I might give it a try, though

it does sound like it makes the whole code less readable. I'll play around with it.

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.

Exactly - I'm creating indexed views, indexes and stored procedures :-)

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.

Ugh.

Thanks for your input!