問題描述

為什么在 sql server 2005 中使用 xml 時(shí)必須將 ARITHABORT 設(shè)置為 ON?我試著研究為什么我必須設(shè)置這個(gè)，但找不到告訴我原因的答案.只是它需要設(shè)置.

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 行時(shí)得到的具體錯(cuò)誤信息:

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

參數(shù)錯(cuò)誤:無法解析插入列表 - 插入失敗因?yàn)橐韵?SET 選項(xiàng)的設(shè)置不正確:'ARITHABORT'.驗(yàn)證 SET 選項(xiàng)是否正確用于索引計(jì)算列和/或查詢通知上的視圖和/或索引和/或 xml 數(shù)據(jù)類型方法.

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.

我的存儲(chǔ)過程在一種環(huán)境中使用 odbc 從 asp.net 調(diào)用運(yùn)行良好.然后當(dāng)我將它移到另一個(gè)時(shí)，我不得不在存儲(chǔ)過程的開頭添加 SET ARITHABORT ON.我在下面包含了存儲(chǔ)過程的相關(guān)部分.以及調(diào)用它的代碼.

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           

調(diào)用代碼

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

推薦答案

我認(rèn)為網(wǎng)上書籍中的這句話暗示了它:當(dāng)您在計(jì)算列或索引上創(chuàng)建或更改索引時(shí)，SET ARITHABORT 必須為 ON意見."所以節(jié)點(diǎn)方法必須在內(nèi)部創(chuàng)建索引視圖或其他東西.但這只是一個(gè)有根據(jù)的猜測(cè).

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.

這篇關(guān)于為什么在 sql server 2005 中使用 xml 時(shí)必須將 ARITHABORT 設(shè)置為 ON?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！