为什么在 sql server 2005 中使用 xml 时必须将 ARITHABORT 设置为 ON? [英] Why do I have to SET ARITHABORT ON when using xml in sql server 2005?

问题描述

为什么在 sql server 2005 中使用 xml 时必须将 ARITHABORT 设置为 ON?我试着研究为什么我必须设置这个，但找不到告诉我原因的答案.只是它需要设置.

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.

这是我取出 SET ARITHABORT ON 行时得到的具体错误信息:

Here is the specific error message I get when I take out the SET ARITHABORT ON line:

参数错误:无法解析插入列表 - 插入失败因为以下 SET 选项的设置不正确:'ARITHABORT'.验证 SET 选项是否正确用于索引计算列和/或查询通知上的视图和/或索引和/或 xml 数据类型方法.

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.

我的存储过程在一种环境中使用 odbc 从 asp.net 调用运行良好.然后当我将它移到另一个时，我不得不在存储过程的开头添加 SET ARITHABORT ON.我在下面包含了存储过程的相关部分.以及调用它的代码.

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.

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 > 0
      ROLLBACK TRANSACTION
   SET @errormsg = N'DATA INSERTION FAILED WITH MESSAGE - ' + ERROR_MESSAGE()
   RAISERROR(@errormsg, 16, 1)
   RETURN
END CATCH

IF @@TRANCOUNT > 0
   COMMIT TRANSACTION

-- return result
SELECT @rindex AS rindex

DROP TABLE #insert_list
DROP TABLE #folder_list

GO           

调用代码

  ' 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) 

推荐答案

我认为网上书籍中的这句话暗示了它:当您在计算列或索引上创建或更改索引时，SET ARITHABORT 必须为 ON意见."所以节点方法必须在内部创建索引视图或其他东西.但这只是一个有根据的猜测.

I'm thinking this statement from books online kind of hints at it: "SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views." So the nodes method must be creating an indexed view internally or something. But this is just an educated guess.

这篇关于为什么在 sql server 2005 中使用 xml 时必须将 ARITHABORT 设置为 ON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！