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
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.
Hello,
ReplyDeleteyour 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.
This work great for me, I'm able to run XML queries on VB.net like:
ReplyDeleteGreetings!
"
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);
"