0

I have a query that strips data from a XML string, inserts it into a table variable and queries it to insert the data into another table. It runs like a charm in my localhost environment, but not in my production SQL Server database. The error message is "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 query notifications and/or xml data type methods.'"

I think it has to do with my XML data. My XML data is read as it is, untyped.

I will appreciate any help with this. It probably has to do with some database setting, or something I have to improve in my query in order to achieve better standards.

Here is an example of the piece of data I am trying to insert:

<data><dataitem evaluateduserid="208" idcompetencylevel="90" /><dataitem evaluateduserid="211" idcompetencylevel="89" /><dataitem evaluateduserid="205" idcompetencylevel="90" /></data>

And here is my query:

ALTER PROC [dbo].[Spel_CM_AssessmentsDataInsertEventItem]
    @IdAssessmentEventItem int,
    @UserCompetencyLevel xml
AS
SET NOCOUNT ON

-- XML Data model:
-- <data>
--  <dataitem evaluateduserid="x" idcompetencylevel="y"></dataitem>
--</data>

DECLARE @TableUserCompetencyLevel table
(
    EvaluatedUserId int,
    IdCompetencyLevel int
)

INSERT INTO 
    @TableUserCompetencyLevel
    (
        EvaluatedUserId,
        IdCompetencyLevel
    )
SELECT
    EvaluatedUserId = aa.Item.value('@evaluateduserid', 'int'),
    IdCompetencyLevel = aa.Item.value('@idcompetencylevel', 'int')
FROM
    @UserCompetencyLevel.nodes('data/dataitem') AS aa(Item)


-- Delete previously existing data. This is useful in case the user is
-- updating an assessment event item
DELETE FROM
    Spel_CM_AssessmentsData
WHERE
    IdAssessmentEventItem = @IdAssessmentEventItem

-- Preparing to insert data...
DECLARE @IdAssessmentEvent int
SELECT @IdAssessmentEvent = da.[IdAssessmentEvent] FROM Spel_CM_AssessmentsEventsItems da WHERE da.[IdAssessmentEventItem] = @IdAssessmentEventItem


-- Inserts data into AssessmentsData table
INSERT INTO Spel_CM_AssessmentsData
(
    [IdAssessmentEvent],
    [IdAssessmentEventItem],
    [EvalatedUserId],
    [IdCompetencyLevel]
)

SELECT
    @IdAssessmentEvent,
    @IdAssessmentEventItem,
    ca.[EvaluatedUserId],
    ca.[IdCompetencyLevel]
FROM
    @TableUserCompetencyLevel ca
WHERE
    (NOT ca.[EvaluatedUserId] IS NULL)
    AND
    ((NOT ca.[IdCompetencyLevel] IS NULL) AND (NOT ca.[IdCompetencyLevel] = ''))

-- Updates the AssessmentsEventsItems table to confirm that the items have been inserted
EXECUTE Spel_CM_AssessmentsEventsItemsUpdate @IdAssessmentEventItem, 1


SET NOCOUNT OFF

Thank you!

1 Answer 1

1

XML data type requires certain SET options to be set in a certain manner. See Setting Options (xml Data Type). Specifically, ARITHABORT must be ON.

Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, but it does not work when I set ARITHABORT ON. I have just tried running the STORED PROCEDURE directly from SQL Management Studio and it works. When my application sends the data, it does not and it is the same data...
It means your application is setting ARITHABORT OFF.
Thanks, I finally found out that I had not placed the ARITHABORT in the right place. It should go inside the sproc, close to the NOCOUNT, not before it as I had tried before. Abraços.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.