問題描述

為什么在 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?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！