March 28, 2008 at 1:19 pm
I have a database, with tables, views, indexed views , stored
procedures, and the data in the database are used and manipulated by
Reporting Services and VB.NET 2003,
i started getting this error:
INSERT failed because the following SET options have incorrect settings:
'ARITHABORT'.,....
I solved this problem by adding this to each sp that i was having these problem
SET ARITHABORT ON
but since i have over 30 sps with the same issue , i ran this script
ALTER DATABASE MyDatabase
SET ARITHABORT ON
GO
and my problem is solved, my questions are:
1. would this affect performance? 2. Is this good practice to change it in the database level? or should just put it in each sp? 3. I read that in sql server 2005 ARITHABORT is ON by default is that true?
4. Is there a query i could run to see if a Database has the ARITHABORT setting ON or OFF? I would like to see how it is in the other databases.
sorry for all the questions,:w00t: thanks in advance!
April 7, 2008 at 8:27 am
> 1. would this affect performance?
No. It shouldn't.
> 2. Is this good practice to change it in the database level? or should just put it in each sp?
For the database-wide setting you should at least consider its effects - they are documented in Books Online (e.g.: http://msdn2.microsoft.com/en-us/library/ms175088.aspx, http://msdn2.microsoft.com/en-us/library/ms190306.aspx).
> 3. I read that in sql server 2005 ARITHABORT is ON by default is that true?
Yes, it is.
> 4. Is there a query i could run to see if a Database has the ARITHABORT setting ON or OFF? I would like to see how it is in the other databases.
In SQL Server 2005:
select [name]
,is_arithabort_on
from sys.databases
In SQL Server 2000:
select [name]
,databasepropertyex([name], 'IsArithmeticAbortEnabled') as IsArithAbortOn
from master..sysdatabases
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 7, 2008 at 9:20 am
thank you!!!! exactly what i needed it. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply