My code inserts data which is passed as a XML from C# code behind which is working fine locally into SQL Server 2008 but on a remote instance of SQL Server 2005 it's throwing the following exception. Please help.
CREATE PROCEDURE SqAnswersInsert @AnswerID INT OUTPUT,
@XmlOptions XML=NULL AS BEGIN
SET ARITHABORT ON
INSERT INTO SqAnswerOptions
SELECT @AnswerID,A.B.value('QuestionOptionID', 'INT') AS QuestionOptionID
--A.B.value('QuestionOptions/QuestionOptionID', 'INT') as QuestionOptionID
FROM @XmlOptions.nodes('/QuestionOptions/Option') A(B)
SET ARITHABORT OFF END
INSERT 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.
When you have an Indexed View in your database, you must execute the SQL command
SET ARITHABORT ON each time you connect to Microsoft SQL Server before executing any other SQL command.
You can eliminate this error by deleting the indexes from your database views
SET ARITHABORT Terminates a query when an overflow or divide-by-zero error occurs during query execution.
1. If SET ARITHABORT is ON and SET ANSI WARNINGS is ON, these error conditions cause the query to terminate. 2. If SET ARITHABORT is ON and SET ANSI WARNINGS is OFF, these error conditions cause the batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation. 3. If SET ARITHABORT is OFF and SET ANSI WARNINGS is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation.