Sunday 18 March 2007

Don't forget SQL Server 2005 'Compatibility Level' (ARITHABORT error on Xml query)

Working on a database recently that has been 'upgraded' from SQL Server 2000 to 2005. As part of the process, I added some 'Tools' stored procedures I commonly use (eg. ScriptDiagram).

For "some reason" that wasn't immediately obvious to me, I could execute these procs without trouble in SQL Server Management Studio BUT when I added some C# code that referenced a '2005-feature' stored procedure (such as using the new xml column.query syntax)...

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 query notifications and/or xml data type methods.

I started off trying to debug the C# code, thinking I'd done something wrong there... but then realised the Database Properties - Options - Compatibility Level must still be set to SQL Server 2000 (80). Changing that to SQL Server 2005 (90) got rid of the SELECT failed error... because I hadn't really 'upgraded' the database at all, just restored a 2000 backup into 2005.

Why the queries work in Studio still confuses me a little bit... but I'll worry about that some other time. Hope that helps someone - I didn't find anything useful on my first google for the error message.
at

2 comments:

  1. Fabrizio17/12/2008, 05:29

    Hello,

    your query runs correctly in Sql Management Studio because it has ARITHABORT set to ON.
    If you set ARITHABORT OFF you'll get the same error as in C#:

    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 query notifications and/or xml data type methods.

    ReplyDelete
  2. Rodrigo24/03/2015, 20:37

    This work great for me, I'm able to run XML queries on VB.net like:
    Greetings!

    "
    SELECT
    N.C.value('ItemId[1]', 'int') ItemId,
    N.C.value('Value[1]', 'varchar(100)') Value
    FROM [NOT].sales
    CROSS APPLY CustomerList.nodes('//ArrayOfCustomers//Customer') N(C);

    "

    ReplyDelete

Note: only a member of this blog may post a comment.