views:

55

answers:

1

Hi, 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  A: 

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.

Remus Rusanu
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...
Marcos Buarque
It means your application is setting ARITHABORT OFF.
Remus Rusanu
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.
Marcos Buarque