SQL Server Errors Handling Overview

以前的編程習慣一直不太好，不管是寫C，C++，C#還是資料庫指令碼一般都是調試測試通過即可，從來不加try/catch等異常處理。現在開始為了良好的編程習慣和一個case，開始學著在有必要處理異常的時候增加異常處理代碼。

SQL Server似乎以前錯誤處理功能不怎麼樣，現在肯定有了很大改進，但我肯定是沒這個資格指手畫腳。。。不管好壞，學會用是第一步。

1. 基礎
1.1 Error Message

一般SQL Server的錯誤訊息如下：
Server: Msg 547, Level 16, State 1, Procedure error_demo_sp, Line 2
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'fk7_acc_cur'.
The conflict occurred in database 'bos_sommar', table 'currencies', column 'curcode'.
The statement has been terminated.

包括以下幾個部分：
Message number：大於50000的為自訂訊息，具體資訊在master..sysmessages表裡，可以用sp_addmessage來添加訊息。
Severity level：0-25，0-10是資訊或警告，編程錯誤為11-16，17-25為資源錯誤、硬體錯誤或SQL Server內部錯誤。>=20的錯誤會導致串連終止。
State：0-127，但是是undocumented。。。特例是對於OSQL和ISQL，如果State為127，會停止並將ERRORLEVEL設定為message number，用來處理安裝指令碼時的錯誤
Line：如果為0說明是procedure調用時出錯。

用戶端決定呈現的格式。

資料庫引擎的錯誤處理分兩個層面：T-SQL層面和應用程式代碼層面。

1.2 T-SQL
1.2.1 TRY...CATCH
T-SQL中的TRY/CATCH用法和C++或C#中差不多，不過看上去沒有Finally。。。
注意點1：在CATCH塊處理完錯誤之後，會跳到END CATCH後的第一句。如果END CATCH已經是預存程序或觸發器的最後一句了，那麼就返回調用預存程序或觸發器的那一句。在TRY塊中所有出錯之後的代碼被忽視。
注意點2：severity（嚴重度）>=20，導致資料庫引擎關閉串連的，不能被處理
注意點3：severity（嚴重度）<=10，視為警告或資訊訊息，，不能被處理
注意點4：要捕捉編譯階段或重編譯階段的錯誤，使用sp_executesql

1.2.2 ERROR 函數
T—SQL中要獲得異常資訊，先要加TRY...CATCH結構，然後在結構內使用ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY 和 ERROR_STATE。看函數名就知道是派什麼用的了。注意點1：如果在TRY...CATCH結構外使用就會返回NULL。（某個case中customer問為什麼會返回NULL，估計就是這個原因。。。）
注意點2：CATCH塊內不管被引用幾次，不管在哪裡引用，返回資訊都一樣。
注意點3：對於嵌套CATCH塊，哪層的異常處理返回哪層的資訊。

1.2.3 @@ERROR
捕捉之前啟動並執行T-SQL語句的錯誤號碼，除了某個特例一般預設為0，對於11以上的message自增。對於SQL Server 2005之前的版本，如果要獲得詳細錯誤資訊有些麻煩，但如果擁有sysadmin許可權的話還是可能實現的（DBCC OUTPUTBUFFER）。一般不可能阻止SQL Server報錯。
其中比較重要的11-16錯誤號碼代表的資訊：
11 – Specified Database Object Not Found
12 – Unused
13 – User Transaction Syntax Error
14 – Insufficient Permission
15 – Syntax Error in SQL Statements
16 – Miscellaneous User Error

注意點1：@@ERROR與運行之後發生的錯誤沒關係。
注意點2：如果是TRY塊出錯，@@ERROR要為CATCH的第一句；如果不是TRY塊出錯，@@ERROR要緊跟著出錯的那一句。
注意點3：@@ERROR必須被儲存到臨時變數中才能對其進行操作

錯誤：

Code
CREATE TABLE notnull(a int NOT NULL)
DECLARE @value int
INSERT notnull VALUES (@value)
IF @@error <> 0
   PRINT '@@error is ' + ltrim(str(@@error)) + '.'
--會返回@@error is 0. 

正確：

Code
CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,
        @value int
INSERT notnull VALUES (@value)
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'
--會返回@err is 515.

注意點4：另外相關的變數：@@rowcount（錯誤影響的行），@@trancount（事務數量）

1.2.4 Uncommittable Transactions（不可提交的事務）
TRY...CATCH中事務可能會開啟但無法提交。當使用DDL或SET XACT_ABORT 設定為 ON 時都會進入該狀態。
這裡涉及到了用XACT_STATE 函數在CATCH塊中測試事務狀態。XACT_STATE有三種傳回值：1為有正常活動事務，0為沒有活動的使用者事務，-1為有活動事務，但存在錯誤無法提交，需要被ROLLBACK TRANSACTION復原。

1.2.5 Stored Procedure傳回值
如果沒有發生錯誤，那麼傳回值為0；如果發生錯誤，可能返回-1或0。

1.2.6 SET XACT_ABORT
SET XACT_ABORT OFF對應Statement-termination
SET XACT_ABORT ON對應Batch-abortion
XACT_ABORT應該是execution action aborted的縮寫。

1.3 Handling Errors and Messages in Applications
針對ODBC，ADO，ADO.NET，SqlClient，OleDb處理方法也不同。

2. 異常發生的背後
針對不同錯誤SQL Server可能的應對方法也不一樣。

可能採取的行動：
1. 語句終止（Statement-termination）。對於open transaction需要手動ROLLBACK TRANSACTION，不然之後的INSERT/UPDATE/DELETE不會自動復原。
2. 範圍中止（Scope-abortion）。A，B都為stored procedure，A調用B，B發生錯誤中止但A調用B之後的代碼照常執行。通常限於編譯錯誤。
3. Batch中止（Batch-abortion）。用戶端提交到SQL Server的整個一批都中止，所有open transaction復原。
4. 串連終止（Connection-termination）。open transaction復原。@@ERROR不能擷取值。通常是嚴重伺服器端錯誤，除了一個例外：sp_OAxxxxx調用自訂stored procedure或OLE objects（應該是和DMO有關）。RAISERROR>=20的話也可以做到。

判斷是語句終止還是batch中止有些困難，具體情況具體分析。

以下的兩種情況不會（但沒有官方說明）發生:
1. 交易回復，但當前batch繼續執行
2. batch中止，但事務不復原。

有一種需要注意的是用戶端的批取消（batch-cancellation）。用戶端可以隨時要求SQL Server中止執行batch，SQL Server可能會立即響應，也可能會延遲一會響應，且不會復原事務（INSERT/UPDATE/DELETE/Trigger中會復原）。更常見的情境是用戶端timeout。ODBC, OLE DB, ADO 和 ADO.Net 都有30秒的預設timeout設定。可以通過設定 .CommandTimeout來避免這個問題。

SQL Server如何決定採取什麼行動？除了特例SET XACT_ABORT OFF（這就是某位客戶的關鍵問題所在，自從SQL Server 2005中說在觸發器中這個特性取消了，明明還可以起作用的說。。。）和Trigger Context（那個客戶的問題偏偏還是觸發器中的SET XACT_ABORT OFF。。。）

對於Trigger Context，所有的錯誤都會終止batch並從這個節點開始復原（例外是RAISERROR和error 266）。另外如果@@trancount為0（對於Trigger Context至少為1）時也會終止。所以Trigger內錯誤處理的模板應該為：

Code
IF EXISTS (SELECT *
           FROM   inserted i
           JOIN   abainstallhistory inh ON i.inhid = inh.inhid
           WHERE  inh.ss_label <> i.ss_label
              OR  inh.ss_label IS NULL AND i.ss_label IS NOT NULL
              OR  inh.ss_label IS NOT NULL AND i.ss_label IS NULL)
BEGIN
   ROLLBACK TRANSACTION
   RAISERROR('Values on ss_label does not match abainstallhistory.', 16, 1)
   RETURN
END 

 

參考內容:
SQL Server BOL - Handling Database Engine Errors
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
http://rusanu.com/2007/10/31/error-handling-in-service-broker-procedures/
http://www.windows-tech.info/15/397cd9af617c7a4e.php