Ajay Suwalka Ajay Suwalka - 7 months ago 7
SQL Question

Getting Error as INSERT failed because the following SET options have incorrect settings

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,

@ClientID INT=NULL,

@VGBID INT=NULL,

@CreatedOn DATETIME=NULL,

@XmlOptions XML=NULL AS BEGIN

SET ARITHABORT ON
INSERT SqAnswers
(ClientID,
VGBID,
[CreatedOn])
VALUES (@ClientID,
@VGBID,
@CreatedOn)

SET @AnswerID=Scope_identity()

INSERT INTO SqAnswerOptions
(AnswerID,
QuestionOptionID)

SELECT @AnswerID,A.B.value('QuestionOptionID[1]', '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.

Answer

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.

From Technet

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.
Comments