[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需要自製的資料列及資料行盤查,當出現問題,自動找出有問題的資料行列。

 


參考

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

@@OPTIONS (Transact-SQL)

設定 user options 伺服器組態選項

SQL Server feedback

ISO 9075-SQL92版