通过

String or Binary data would be truncated: replacing the infamous error 8152

  • 项目

In the recent announcement at Ignite 2018 on the release of SQL Server 2019 CTP 2.0, the new Big Data Clusters, data virtualization, support for UTF-8, and Intelligent Query Processing were highlights. But we have also previewed work being done to address the infamous error message “String or binary data would be truncated”.

This error condition may happen for example when you implement an ETL between a source and a destination that does not have matching data types and/or length. In this context, the message "String or binary data would be truncated" is one of the most time-consuming troubleshooting processes to engage in, especially in large datasets. You probably know about and voted for this feedback item before.

Let’s see an example of what happens when you insert data into a column whose size is not big enough to store it:

 USE [AdventureWorks2016CTP3]
GO
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

You receive following error message, which admittedly is not very helpful:

 Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.

We heard that. Which is why SQL Server 2019 introduces a new message, with additional context information. For the same operation, the new error message outputs the following:

 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.

Ok, the new error message provides more context, and now I have the resulting truncated value (not the source value). This is simplifying the troubleshooting process, because now I know the truncated value starts with ‘1231736Vi8604' – that’s 13 characters. And so I can go back to my data source, and locate the source record and its length:

 SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])
FROM [Sales].[SalesOrderHeader]
WHERE CreditCardApprovalCode LIKE '1231736Vi8604%'

image

It’s 14 characters, and in my table definition I have a NVARCHAR(13). Well, I know what I need to do: change my table data type length.

This new message is also backported to SQL Server 2017 CU12 (and in an upcoming SQL Server 2016 SP2 CU), but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.

Note that for now, even in SQL Server 2019 CTP 2.0 the same trace flag 460 needs to be enabled. In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.

Is there a limit to how much of my truncated string this error can return?
Let’s run a small test inserting a 123 character string into a 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

Although my string gets truncated at 120 characters, the offending value that is shown is truncated to the first 100 characters:

 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 '.

Still plenty to find offending values in most data sources.

Pedro Lopes (@SQLPedro) – Senior Program Manager

Comments

  • Anonymous
    October 25, 2018
    Wahoo, thank you guys for finally resolving this, albeit a decade (!!!) after it was first requested on Connect back in 2008! Hopefully we can look forward to one or two of the other top requests making it into the product over the course of our lifetimes :-)
  • Anonymous
    October 25, 2018
    This is really a good news. I hope MS can somehow budget at least 40% resources in fixing the existing "eye-sores" to make SQL Server a better / more robust system and the rest 60% budget about developing new features.