SQL Agent Insert Error - Incorrect SET options

• paul-941374

  Say Hey Kid

  Points: 701

  More actions

  December 4, 2008 at 10:47 am

  #202468

  I'm getting errors when running the following procedure using SQL Agent. I've tried adjusting yTable to no effect, so now think it maybe to do with xTable and the XML datatype. This is all new to me so any assistance would be greatly appreciated.

  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. [SQLSTATE 42000] (Error 1934).

  DECLARE @xmlEvents XML

  SELECT @xmlEvents = BulkColumn

  FROM OPENROWSET(BULK 'C:\eventdata.xml', SINGLE_BLOB) AS x

  CREATE TABLE xTable

  (

  xTable_ID INT IDENTITY PRIMARY KEY,

  xCol XML

  ) ;

  INSERT INTO xTable ( xCol )

  SELECT @xmlEvents

  INSERT INTO yTable

  (

  value1,

  value2

  )

  SELECT

  x.eventDetails.value('typeID[1]', 'int'),

  x.eventDetails.value('agelo[1]', 'varchar(25)')

  FROM @xmlEvents.nodes('//FLSDEvents/event') AS x (eventDetails)

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  December 4, 2008 at 12:11 pm

  #907846

  I might be off on this, but did you try this;

  INSERT INTO xTable ( xCol )

  VALUES ( @xmlEvents)

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Carolyn Richardson

  SSCrazy Eights

  Points: 8357

  More actions

  December 4, 2008 at 12:21 pm

  #907856

  From BOL

  Syntax

  SET ARITHABORT { ON | OFF }

  SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.

  So you could try SET ARITHABORT ON at the start of the query, provided you test that the results are as expected.

  Facts are stubborn things, but statistics are more pliable - Mark Twain
  Carolyn
  SQLServerSpecialists[/url]

• paul-941374

  Say Hey Kid

  Points: 701

  More actions

  December 4, 2008 at 12:30 pm

  #907864

  Thanks Carloyn, that did the trick!

  I should have been more specific Grant, the problem was not with the first Insert but the second which was selecting XML data. Not really sure why Carloyn's solutions works, but it does. Excellent!

  Many thanks to all.