最近同事在匯入大量資料常碰到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需要自製的資料列及資料行盤查,當出現問題,自動找出有問題的資料行列。
參考
What is the impact of setting SET ANSI_WARNINGS OFF?
SET ANSI_WARNINGS (Transact-SQL)
binary or string data would be truncated => error message enhancments