473,574 Members | 2,556 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

setting and restoring multiple connection options

I need to set multiple values for some SQL statements, for example

SET NUMERIC_ROUNDAB ORT OFF
GO
SET
ANSI_PADDING,AN SI_WARNINGS,CON CAT_NULL_YIELDS _NULL,ARITHABOR T,QUOTED_IDENTI F
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.
Jul 20 '05 #1
3 5285
Florian (RE************ **********@gmx. net) writes:
I need to set multiple values for some SQL statements, for example

SET NUMERIC_ROUNDAB ORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIE LDS_NULL, ARITHABORT,
QUOTED_IDENTIFI ER, 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
Jul 20 '05 #2
> 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!
Jul 20 '05 #3
> > 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_1ng 3.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_YIE LDS_NULL
NUMERIC_ROUNDAB ORT
QUOTED_IDENTIFI ER

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******** ***********@new sread2.news.pas .earthlink.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!

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3005
by: dmiller23462 | last post by:
So here's my problem.....I need to set up different email distributions based on which option in the following Select form has been chosen....For instance if "Putaway" is chosen it needs to email User1@here.whatever and User4@here.whatever but if "Loaded" is chosen it needs to email User2@here.whatever and User3@here.whatever, etc, etc... ...
3
3027
by: Max Weber | last post by:
Try to run the code below in a page. You will notice than when you switch the multiple attribute of the SELECT tag, only one option is displayed as selected although multiple options have ben created as selected. May somebody give me an explanation ? <input type="button" value="test" onclick="test()"/> <select name="tagSelect"...
6
4973
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a...
1
1626
by: Tom | last post by:
I have a large application; lots of forms, multiple dynamic DLLs, etc. I also have, in the appliation, a general 'Preferences' class object (which is in itself a separate DLL, and I just include a reference to it so I can instantiate it at the beginning) which stores all my user preferences. I serialize the data to and from an XML file,...
2
3679
by: Fischer Ulrich | last post by:
Hi I have a problem with the restoring of a database which uses tsearch2. I made a backup as discribed in 'tsearch-v2-intro' on the tsearch2 page. Now i'm trying to restore it into a testdatabase. The problem is, that the entries of the relations 'pg_ts_cfg', 'pg_ts_cfgmap', 'pg_ts_dict' and 'pg_ts_parser' are not correctly restored. I get...
1
5318
by: Yelena Varshal via AccessMonster.com | last post by:
Hello, What are the pre-requisites / conditions for the ability to create multiple connections to MS ACCESS database and what is the precedence of its application? adModeShareDenyNone in the code, Exclusive checkbox in the ODBC source, Tools->Options->Advanced->Shared in the Database Options, what else? I have a problem when multiple...
5
2431
by: Jai | last post by:
Hi, I am in a problem of sending mass emails(newsletter) to my website members. Actually my problem is this: I want to send newsletter to my website members. But I had given a facility for each member to choose different category of products or all products newsletter.
1
6465
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting" setting to be "E_ALL", notices are still not getting reported. The perms on my file are 664, with owner root and group root. The php.ini file is located...
4
4385
by: rn5a | last post by:
A Form has 2 select lists. The 1st one whose size is 5 (meaning 5 options are shown at any given time) allows multiple selection whereas the 2nd one allows only 1 option to be selected at a time. When an option is selected in the 2nd select list, the ASP page posts itself. Assume that the 1st select list has the following 10 options (note...
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8257
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7828
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8113
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6476
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5634
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3765
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2253
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1076
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.