SET ARITHABORT must be ON for XML. Why?

Question

SET ARITHABORT must be ON for XML. Why?

ben.brugman
SSChampion

Points: 13369
More actions
October 10, 2011 at 7:13 am

#258655

Hello All,

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

WHY?

We got the above error. Setting ArithAbort on prevented the error.

Where I would expect that setting ArithAbort on would generate more errors instead of less. So setting this option on the queries should be more strict.

For the record we are using XML, not the computed columns or the filtered indexes etc.

Can anybody explain the Why?

And/Or.

Can anybody give a link (Knowledge base for example) that this is 'standard'. So that we can put this in the deployment documentation of a product.

Thanks for your time and attention.

Ben Brugman

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Answer 1

Here's the link for the standard needed: http://msdn.microsoft.com/en-us/library/ms190306.aspx

As for why, it has to do with errors vs null values. Read the data I linked to, it'll probably make it more clear for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Answer 2

Thank you for your time and answer.

In our situation OFF produces a 'fatal' error, and with ON the select get's completed.

The provided link does describe the Set errorabort, but the description given is that ON produces 'more' errors than OFF does. The other way around than what we are experiencing.

Thanks again for your attention,

Ben Brugman

Answer 3

They both will give you errors/warnings about the data, but Arithabort Off will give an error if you use it where it needs to be on.

So, yeah, you'll get more errors with it off than on, if you use it on indexed views, XML DML, persisted computed columns, et al. Use it outside of those circumstances and you'll get less errors, assuming you don't do a lot of dividing by 0 or similar things that it alerts/errors on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon