title: Why do I have to SET ARITHABORT ON when using xml in sql server 2005?
body: <p>Why do I have to SET ARITHABORT ON when using xml in sql server 2005?
  I tried researching why I have to set this but couldn't find an answer that told me why.  Only that it needs to be set.</p>
  
  <p>Here is the specific error message I get when I take out the SET ARITHABORT ON line:</p>
  
  <blockquote>
    <p>PARAMETER ERROR: INSERT LIST COULD NOT BE PARSED - 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.</p>
  </blockquote>
  
  <p>My stored procedure worked fine called from asp.net using odbc in one environment.  Then when I moved it to another, I had to add SET ARITHABORT ON in the beginning of the stored procedure.  I include the relevant sections of the stored procedure below.  And the code that is calling it.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[myproc]
     @ruserid             varchar(8),
     @folder_list         xml,
     @insert_list         xml
  AS
  
  SET NOCOUNT ON
  SET ARITHABORT ON
  
  DECLARE @rindex integer
  DECLARE @errormsg nvarchar(4000)
  DECLARE @folder_cnt integer
  DECLARE @insert_cnt integer
  
  
  SET @rindex = -1
  
  -- temp table to hold inserts
  CREATE TABLE #insert_list (rowidx integer IDENTITY(1,1), insertdesc varchar(96) COLLATE database_default, insertfolder integer)
  
  -- temp table to hold folders
  CREATE TABLE #folder_list (rowidx integer IDENTITY(1,1), folderdesc varchar(144) COLLATE database_default, insertfolder integer)
  
  -- insert inserts to make sure data is compatible in type
  BEGIN TRY
     INSERT INTO #insert_list (insertdesc, insertfolder)
     SELECT insert_list.listitem.value('@insertdesc', 'varchar(96)'), insert_list.listitem.value('@insertfolder', 'integer')
     FROM @insert_list.nodes('/Root/Insert') AS insert_list(listitem)
  END TRY
  BEGIN CATCH
     SET @errormsg = N'PARAMETER ERROR: INSERT LIST COULD NOT BE PARSED - ' + ERROR_MESSAGE()
     RAISERROR(@errormsg, 16, 1)
     RETURN
  END CATCH
  
  -- insert folders to make sure data is compatible in type
  BEGIN TRY
     INSERT INTO #folder_list (insertfolder, folderdesc)
     SELECT folder_list.listitem.value('@insertfolder', 'integer'), folder_list.listitem.value('@folderdesc', 'varchar(144)')
     FROM @folder_list.nodes('/Root/Folder') AS folder_list(listitem)
  END TRY
  BEGIN CATCH
     SET @errormsg = N'PARAMETER ERROR: FOLDER LIST COULD NOT BE PARSED - ' + ERROR_MESSAGE()
     RAISERROR(@errormsg, 16, 1)
     RETURN
  END CATCH
  
  -- insert rows
  BEGIN TRANSACTION
  
  BEGIN TRY
  
  INSERT INTO my_folder_request (ruserid)
  VALUES ( @ruserid )
  
  SET @rindex = SCOPE_IDENTITY()
  
  INSERT INTO my_insert_request (rindex, insertdesc, insertfolder)
  SELECT @rindex, #insert_list.insertdesc, #insert_list.insertfolder
  FROM #insert_list
  ORDER BY #insert_list.rowidx
  
  INSERT INTO my_folder_desc (rindex, insertfolder, folderdesc)
  SELECT @rindex, #folder_list.insertfolder, #folder_list.folderdesc
  FROM #folder_list
  ORDER BY #folder_list.rowidx
  
  END TRY
  BEGIN CATCH
     IF @@TRANCOUNT &gt; 0
        ROLLBACK TRANSACTION
     SET @errormsg = N'DATA INSERTION FAILED WITH MESSAGE - ' + ERROR_MESSAGE()
     RAISERROR(@errormsg, 16, 1)
     RETURN
  END CATCH
  
  IF @@TRANCOUNT &gt; 0
     COMMIT TRANSACTION
  
  -- return result
  SELECT @rindex AS rindex
  
  DROP TABLE #insert_list
  DROP TABLE #folder_list
  
  GO           
  </code></pre>
  
  <p>Calling Code</p>
  
  <pre><code>  ' build odbc command for inserting creation request
    intRequestIndex = 0
    cmdAddRequest = New System.Data.Odbc.OdbcCommand
    cmdAddRequest.CommandType = CommandType.StoredProcedure
    cmdAddRequest.CommandTimeout = 60
    cmdAddRequest.CommandText = "{CALL myproc ( ?, ?, ?)}"
  
    ' add parameters to odbc command
    cmdAddRequest.Parameters.Add("@ruserid", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strUID)
    cmdAddRequest.Parameters.Add("@folder_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmFolderList.ToArray())
    cmdAddRequest.Parameters.Add("@insert_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmInsertList.ToArray())
  
    ' run odbc command returning info about results
    cmdAddRequest.Connection = Me.ODBCConnection()
    Try
       rdrRequestData = cmdAddRequest.ExecuteReader(CommandBehavior.CloseConnection) 
  </code></pre>