SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환

 

·         Version : SQL Server 2019

 

SQL Server 2019 CTP 2.0에서 문자열 잘림에 대한 에러메시지가 향상되었다일반적으로 ETL 작업  많이 겪는 문제중 하나가 “String or binary data would be truncated”이다 오류 조건은 일치하는 데이터 유형 / 길이가 없는 소스와 대상간에 ETL 구현할  발생할  있다특히 대형 데이터 세트에서 가장 시간이 많이 걸리는 프로세스중 하나이다아래 스크립트는 크기가 충분하지 않는 열에 해당 열보다  데이터를 삽입하면 어떻게 되는지 확인하는 예제 스크립트이다.

DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]

GO

CREATE TABLE [Sales].[SalesOrderHeaderTest](

    [SalesOrderID] [INT] NOT NULL,

    [CustomerID] [INT] NOT NULL,

    [CreditCardApprovalCode] [nvarchar](13) NULL

)

GO

 

INSERT INTO [Sales].[SalesOrderHeaderTest]

SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]

FROM [Sales].[SalesOrderHeader]

GO

 

 스크립트를 실행하면 아래와 같은 오류 메시지가 표시 되는데문제 해결에 크게 도움이 되지 않는다.

Msg 8152, Level 16, State 4, Line 10

String or binary data would be truncated.

The statement has been terminated.

 

SQL Server 2019 CTP 2.0에서는 추가 컨텍스트 정보와 함께  메시지를 나타낸다동일한 작업의 경우  오류 메시지는 아래과 같이 출력된다.

Msg 2628, Level 16, State 1, Line 14

String or binary data would be truncated in table 'AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest', column 'CreditCardApprovalCode'. Truncated value: '1231736Vi8604'.

The statement has been terminated.

 

 새로운 오류 메시지는  많은 컨텍스트를 제공하고 결과 (소스값 아님) 보여준다결과를 보면 문제가 발생한 부분이 ‘1213736Vi8604’ 시작한다는 것을   있다 길이가 13 이다다시 소스로 돌아와 해당 레코드의 길이를 살펴보면 14 인것을   있다그러므로 데이터가 잘리는 오류가 발생한다해당 오류를 확인하고 적절한 데이터 길이로 컬럼을 변경해서 해결해야한다.

 SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode],LEN([CreditCardApprovalCode])

FROM [Sales].[SalesOrderHeader]

WHERE CreditCardApprovalCode LIKE '1231736Vi8604%'

 

 

 새로운 메시지는 SQL Server 2017 CU12  SQL Server 2016 SP2 CU에도 적용되지만 기본적으로 활성화 되지 않는다추적플래그 460 활성화하여 세션ID 또는 서버 레벨에서 메시지 ID 8152 2628 바꿀 필요가 있다현재로서는 SQL Server 2019 CTP 2.0에서도 동일한 추적 플래그 460 사용할  있어야 한다향후 SQL Server 2019 릴리스에서는 기본적으로 메시지 2628 8152 대체한다.

 오류메시지가 반환할  있는 잘린 문자열에 대한 최대 크기는 100 까지만 표시된다아래 스크립트는 123자의 문자를 VARCHAR(120) 삽입하는 스크립트이다.

CREATE TABLE myTable (myString VARCHAR(120));

GO

INSERT INTO myTable

VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.')

GO

 

 스크립트를 실행하면 문자여링 120자에서 잘리지만 실제 표시되는 오류는 처음100 까지만 표시된다.

Msg 2628, Level 16, State 1, Line 30

String or binary data would be truncated in table 'AdventureWorks2016CTP3.dbo.myTable', column 'myString'. Truncated value: 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore '.

 

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/

 

 

2018-10-25 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, SQL 2019, TF 460, 문자 잘림 오류



출처: https://sqlmvp.tistory.com/1274?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.