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!