oracle set ansi_nulls off,[SQL Server][Data Exception]寫入或更新資料時，讓SQL暫時自動截斷過長的字串...

最近同事在匯入大量資料常碰到SQL Msg 8152, String or binary data would be truncated，中文訊息是是字串或二進位資料會被截斷。

除了找出有問題的資料行與列或是將欄位型別改為varchar(max)兩個方法外，能不能在SQL裡自動截斷過長的資料，不要讓SQL出現錯誤?

今天錯誤的主角，Msg 8152本人開箱照

Entity Framework

透過DbEntityValidationException中的ValidationErrors，還可以知道是哪一個欄位出問題。

Dapper

看到這些錯誤，能不能在SQL裡自動截斷過長的字串資料，別讓SQL出聲?

答案是可以的! 在目前的工作階段的連接關閉ANSI警示(SET ANSI_WARNINGS OFF)就行了，馬上用T-SQL試給同事看。

查看目前連接中的SET選項

首先從SQL管理工具SSMS連接，Ctrl+ N新增查詢視窗，輸入DBCC USEROPTIONS觀察SSMS管理工具連接預設的SET選項:

DBCC USEROPTIONS;

預設SSMS管理工具的連接是有啟動ansi_warnings

關閉ANSI警示的測試

USE tempdb

create table ISO_3166

(

Id INT IDENTITY,

NAME varchar(20),

A3 varchar(3),

N3 varchar(3)

CONSTRAINT PK_ISO_3166 PRIMARY KEY (Id)

)

測試寫入資料，第三筆的3位數字國別碼故意放成4位數。

INSERT INTO ISO_3166

VALUES ('FRANCE', 'FRA', '250'), ('ITALY', 'ITA', '380'), ('NORWAY', 'NOR', '5781')

一寫入，果然發生8152,字串或二進位資料會被截斷。

關閉ANSI警示，再試一次。

--關閉ANSI警示

SET ANSI_WARNINGS OFF;

--新增資料

INSERT INTO ISO_3166

VALUES ('FRANCE', 'FRA', '250'), ('ITALY', 'ITA', '380'), ('NORWAY', 'NOR', '5781')

--查詢資料

SELECT * FROM ISO_3166

挪威的4位數位國碼5781自動截斷成578寫入TABLE了!!!

為什麼要出現警示?

其實是SQL Server按照國際標準而發出的警示，在ISO 9075，也就是資料庫SQL語言的國際標準中，大約定義了將近20種資料異常(data exception)，

像是除以零或是字串過長截斷的錯誤都是

data exception-division by zero(22-012)

data exception-string data, right truncation  (22-001)

治本

有治標，也想換個角度來治本，除了Entity Framework，透過Dapper、SqlCommand或是T-SQL指令時，能不能像Oracle 10g以後的版本，能清楚標示是哪一個欄位過長?(ORA-01401 inserted value too large for column xxx)

其實這個問題從Microsoft Connect收集回饋的時代，一直到uservoice都持續有人回饋，而且目前還是SQL回饋排名裡，大家投票最高的一項!

好消息是，未來應該會強化錯誤訊息，至少可以像Oracle一樣知道是哪一個欄位。

副作用

由於SET ANSI_WARNINGS 可以指定數個錯誤狀況的在ISO 標準行為。使用時會影響下列狀況：

當設為 ON 時，如果彙總函式 (如 SUM、AVG、MAX、MIN、STDEV、STDEVP、VAR、VARP 或 COUNT) 中出現 Null 值，就會產生警告訊息。 當設為 OFF 時，不會產生警告訊息。

當設為 ON 時，除以零和算術溢位錯誤會造成陳述式的回復，且會產生錯誤訊息。 當設為 OFF 時，除以零和算術溢位錯誤會造成傳回 Null 值。

當設為 ON時，如果嘗試對新值長度超出資料行大小上限的 character、Unicode 或 binary 資料行執行 INSERT 或 UPDATE，INSERT 或 UPDATE 就會依 ISO 標準的指定加以取消。 字元資料行尾端的空格會被忽略，二進位資料行尾端的 Null 也會被忽略。 當它是 OFF 時，便會將資料 截斷成為資料行大小，陳述式會繼續作業。

小結

SET ANSI_WARNINGS OFF是臨時解決方式，不是長久之計。

資料字典設計資料表很重要!欄位名稱相同，應該資料型別和長度都一致。

BulkCopy需要自製的資料列及資料行盤查，當出現問題，自動找出有問題的資料行列。

參考