Setting ARITHABORT in connection string

[Glen Low]

Color me stupid but MSDN was no help and neither was google.

I have an OLEDB connection string that I want to have ARITHABORT set
to ON. How do I do it? (Note it must be in the connection string, vs.
an explicit command.)

Cheers,
Glen Low, Pixelglow Software
www.pixelglow.com

[Dan Guzman]

I don't believe there's a way to specify ARITHABORT ON in the connection
string. However, you can turn this on as the database level default with
ALTER DATABASE. You can also specify ARITHABORT ON at the server level via
the 'user options' sp_configuration parameter.
--
Hope this helps.

Dan Guzman
SQL Server MVP

Post by Glen Low
Color me stupid but MSDN was no help and neither was google.
I have an OLEDB connection string that I want to have ARITHABORT set
to ON. How do I do it? (Note it must be in the connection string, vs.
an explicit command.)
Cheers,
Glen Low, Pixelglow Software
www.pixelglow.com

[g***@pixelglow.com]

Post by Dan Guzman
I don't believe there's a way to specify ARITHABORT ON in the

connection

Post by Dan Guzman
string. However, you can turn this on as the database level default with
ALTER DATABASE. You can also specify ARITHABORT ON at the server level via
the 'user options' sp_configuration parameter.

The confusing thing for me is, isn't ARITHABORT a connection setting?
Consider the following:

DatabaseA -- database default: ARITHABORT on
DatabaseB -- database default: ARITHABORT off

Make an OLEDB connection to DatabaseA. Then do a SELECT * FROM
DatabaseB.dbo.TableB. Does this now use ARITHABORT ON also?

My difficulty is I'm using SQLXML and mostly XPath queries. I can set
ARITHABORT on using a SQL query but it will be invoked on each template
call, I'd rather set it once and for all in the connection which
hopefully will be pooled.

Cheers,
Glen Low, Pixelglow Software
www.pixelglow.com

[Dan Guzman]

Post by g***@pixelglow.com
DatabaseA -- database default: ARITHABORT on
DatabaseB -- database default: ARITHABORT off
Make an OLEDB connection to DatabaseA. Then do a SELECT * FROM
DatabaseB.dbo.TableB. Does this now use ARITHABORT ON also?

The effective connection setting is inherited from the current default
database context (DatabaseA). Consequently, ARITHABORT is ON for this
query.

The rules for SET option precedence are listed in the Books Online
<createdb.chm::/cm_8_des_05_1ng3.htm>. In summary:

- explicit SET by application
- connection string specification
- ODBC DSN setting
- provider default setting
- database default setting
- server default setting

Post by g***@pixelglow.com
My difficulty is I'm using SQLXML and mostly XPath queries. I can set
ARITHABORT on using a SQL query but it will be invoked on each template
call, I'd rather set it once and for all in the connection which
hopefully will be pooled.

Another option is to specify ARITHABORT ON as the server default. This way,
ARITHABORT ON will be used set off at a higher level. You can use
sp_configure to specify ARITHABORT ON by turning on 'user options' bit 64.
--
Hope this helps.

Dan Guzman
SQL Server MVP

Post by g***@pixelglow.com

Post by Dan Guzman
I don't believe there's a way to specify ARITHABORT ON in the

connection

Post by Dan Guzman
string. However, you can turn this on as the database level default

with

Post by Dan Guzman
ALTER DATABASE. You can also specify ARITHABORT ON at the server

level via

Post by Dan Guzman
the 'user options' sp_configuration parameter.

The confusing thing for me is, isn't ARITHABORT a connection setting?
DatabaseA -- database default: ARITHABORT on
DatabaseB -- database default: ARITHABORT off
Make an OLEDB connection to DatabaseA. Then do a SELECT * FROM
DatabaseB.dbo.TableB. Does this now use ARITHABORT ON also?
My difficulty is I'm using SQLXML and mostly XPath queries. I can set
ARITHABORT on using a SQL query but it will be invoked on each template
call, I'd rather set it once and for all in the connection which
hopefully will be pooled.
Cheers,
Glen Low, Pixelglow Software
www.pixelglow.com