I am getting the following error.
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Exception: System.Data.SqlClient.SqlException: UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. 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 have tried the following ...
1. SELECT DATABASEPROPERTYEX('[myDatabase]', 'IsNumericRoundAbortEnabled'). This returns a value of 0 (zero).
2. sp_dboption '[myDatabase]', 'NUMERIC_ROUNDABORT'. This returns the following
OptionName CurrentSetting
---------------- --------------
numeric roundabort OFF
3. I have added the following after the begin statement in my sp.
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
5. The part that gets confusing is that when I add oSqlCmd.CommandText = "SET NUMERIC_ROUNDABORT OFF" to my code it works. I thought that the default value for NUMERIC_ROUNDABORT is OFF. Also if I run the same update statement as a query in SQL 2005 it works without any errors.
Please help. I am trying to avoid adding oSqlCmd.CommandText = "SET NUMERIC_ROUNDABORT OFF" before every call to the db.