Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using SQL Server 2008.

In my stored procedures, I often have to call for XML data type, functions (like split).

Is it possible to permanently enable arithmentic abort for a database? I know it can be done using properties window > options but is there any script i can use to turn it on?

The reason to look for script is obvious; i have too many DBs to do it manually.

I assume set arithabort off and set arithabort on turns on arithabort only for the context.

share|improve this question
up vote 4 down vote accepted

As well as Marian's answer which is SQL Server Instance level, you can use ALTER DATABASE to set it individually. You can also use it on the model database so that all new databases inherit the setting

share|improve this answer
you showed me the exact requirement.... – Umer Jan 27 '12 at 9:36
i wonder if i can voteup with this much privilege :S – Umer Jan 27 '12 at 9:37

According to MSDN, section "Considerations when to use Set options":

"The user options setting of sp_configure allows for server-wide settings and works across multiple databases. This setting also behaves like an explicit SET statement, except that it occurs at login time."

So you might want to look on the server system configuration procedure, sp_configure. It has an option called "user options", where you can configure some things at login time.

Your setting could be something like:

exec sp_configure 'user options', 64

Other helpful details can be found in this forum thread on

share|improve this answer
wow.... great, wish i could vote up :( – Umer Jan 27 '12 at 9:38
If you liked the answer, then you can vote up. You can't mark 2 answers, but you can vote up as many answers as you want :-). – Marian Jan 29 '12 at 11:47
as soon as i could, i did :) – Umer Jan 30 '12 at 5:28

Hi There are default settings for ARITHMETIC ABORT AND ANSI_NULLS, etc at server level in 'Database Connection' Properties of the server in Management studio.

These are overridden by the individual settings of each database, which can be seen in the properties of each database - right click the db > properties > selection option on left hand side.

You should also consider that a number of client libraries will override both of these settings when connecting.

share|improve this answer
+1 for mentioning that most client SET this option when connecting, and this overrides database- and server-level defaults. – Paul White Dec 1 '12 at 4:06

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.