Arithabort error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Arithabort error

Author

Topic

jaynichols
Starting Member

18 Posts

Posted - 2008-10-15 : 12:15:40

I'm pretty sure this proc has worked before, but now I get the following error when I try to execute the proc: Msg 1934, Level 16, State 1, Procedure Audit, Line 11
SELECT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS, ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Clicking on the error it points to this line of code: @5WK_SALES_C bigint, in a declare statement. I have not set the ANSI_Warnings or ARITHABORT. When I set them both off I get more errors, so i just left the setting of these two switches out of the proc compile.

I've predicated all divide statements with an "if divisorfield <> 0"

Dirt biking forever!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 13:06:18

whats your current setting for ANSI_Warnings or ARITHABORT? what does below return?
sp_dboptions 'yourdbname'

jaynichols
Starting Member

18 Posts

Posted - 2008-10-15 : 14:08:22

exec master.sys.dboption 'DW_PROD'

trunc. log on chkpt.
torn page detection
auto create statistics
auto update statistics

Dirt biking forever!

jaynichols
Starting Member

18 Posts

Posted - 2008-10-15 : 14:22:15

When I put
set ARITHABORT on
go
set ANSI_Warnings on
go
before the alter proc line the proc compiles and runs without error. But when I go to SSRS and try to execute it from a report it fails with the message about ARITHABORT is not set correctly.

Dirt biking forever!

jaynichols
Starting Member

18 Posts

Posted - 2008-10-15 : 14:28:11

I found that if I set ARITHABORT and ANSI_Warnings ON in the proc, the proc runs in the SSRS. So, should the ARITHABORT and ANSI_Warnings be set ON always for a db? If so, how do you set it for the entire db?

Dirt biking forever!

jaynichols
Starting Member

18 Posts

Posted - 2008-10-15 : 15:07:47

I found the documentation that gives the "Required Value" for the following options when using any computed columns in a table or view. I found these all set to OFF:

ANSI_NULLS
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

They can be changed in the properties pane in the options tab for a database. i wonder why SQL defaults to OFF for these options?

Dirt biking forever!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources