Monday, October 19, 2009

ARITHABORT and Xml Datatype on SQL

While using XML datatype on Sql 2005 or 2008, I got into a strange problem. The sql queries used work perfectly when I run it on query analyzer but it used to fail when I put that on a stored procedure. The sql was using xml datatype and it was performing some xml operations.

After researching, I found that if we use xml datatype, we need to set ARITHABORT ON.
This is the requirement as stated here

"Currently, there is a reqiurement that ARITHABORT must be ON when you use XML methods and XQuery. However, as long as the compatibility level is >= 90,ANSI_WARNINGS on implies ARITHABORT ON, so this check is not needed. Many client APIs connect by default with this setting off, but ANSI_WARNINGS on,so users need to take extra precautions to deal with this and it can causeconfusion and misery. For instance, I recently encountered a case where someonerun into his Agent job failing because of this"

So make a point to set ARITHABORT ON whenever you use xml datatype on your stored procedures.

No comments:

Post a Comment