The most popular and annoying error message every SQL Server developer runs into during development. String or Binary data would be truncated. For instance, annoying when an Insert statement with 100 columns starts raising truncation error. However, we do not have an easy way out to find the string value and column name generating truncation error.
Most importantly, I was trying to find the column and row generating a truncation error message. We had to use length function to find the longest string in input data. Furthermore, Dividing the load to see which batch of rows raising truncation error.
On the other hand, SQL Server has evolved over the years. As a result, an easy solution now available to show the column name and data row causing truncation error.
With SQL Server 2016 & 2017 edition we have trace flag 460. SQL Server 2019 with compability level 150 includes this by default with VERBOSE_TRUNCATION_WARNINGS configuration setting if we need to fall back to old error Msg 8152.
SQL Server 2016 & 2017 – Trace Flag 460
Using the following example where one of the row and column exceeds length limit for a column defined in table structure. Insert statements resulting into error generating truncation error message with Msg 8152.
ALTER DATABASE PracticeDB SET COMPATIBILITY_LEVEL = 140; --SQL Server 2017 GO DROP TABLE IF EXISTS dbo.Players; CREATE TABLE dbo.Players ( Id INT IDENTITY(1,1) PRIMARY KEY ,[Name] VARCHAR(15) ,[Address] VARCHAR(20) ); INSERT INTO dbo.Players VALUES ('Rafal Nadal','Lives in Spain, Football') ,('Roger Federer', 'Lives in Switzerland, Famous for chocolates') --Error Row ,('Andy Murry','Lives in London, too costly');
Above Insert statement generates errors as expected. Let’s turn on trace flag 460 to find out which column and data row is exceeding the limit resulting into truncation error.
ALTER DATABASE PracticeDB SET COMPATIBILITY_LEVEL = 140; --SQL Server 2017 GO DBCC TRACEON(460,-1); INSERT INTO dbo.Players VALUES ('Rafal Nadal','Lives in Spain') ,('Roger Federer', 'Lives in Switzerland, Famous for chocolates') --Errow Row ,('Andy Murry','Lives in London,costly'); DBCC TRACEOFF(460,-1);
Turning on trace flag 460 generates different error messages thus providing meaningful information to quickly fix the truncation error.
Msg 2628, Level 16, State 1, Line 3
String or binary data would be truncated in table ‘PracticeDB.dbo.Players’,
column ‘Address’. Truncated value: ‘Lives in Switzerland’.
The statement has been terminated.
SQL Server 2019 – VERBOSE_TRUNCATION_WARNINGS = ON | OFF
With SQL Server 2019, Microsoft has made this feature available by default, through scoped configuration settings. If you set VERBOSE_TRUNCATION_WARNINGS = OFF, overriding the default setting, it will give the old Msg 8152, string or binary data would be truncated error.
Make sure you are on compatibility level 150 with SQL Server 2019 to use this feature. Otherwise, you need to depend on Trace flag 460.
ALTER DATABASE PracticeDB SET COMPATIBILITY_LEVEL = 150; --SQL Server 2019 GO DROP TABLE IF EXISTS dbo.Players; CREATE TABLE dbo.Players ( Id INT IDENTITY(1,1) PRIMARY KEY ,[Name] VARCHAR(15) ,[Address] VARCHAR(20) ); INSERT INTO dbo.Players VALUES ('Rafal Nadal','Lives in Spain, Football') ,('Roger Federer', 'Lives in Switzerland, Famous for chocolates') --Error Row ,('Andy Murry','Lives in London, too costly');
Above Insert, statement generates following error message without using trace flag configuration. In case you want to see old Msg 8152 error message. You need to set VERBOSE_TRUNCATION_WARNINGS = OFF.
Msg 2628, Level 16, State 1, Line 3
String or binary data would be truncated in table ‘PracticeDB.dbo.Players’,
column ‘Address’. Truncated value: ‘Lives in Switzerland’.
The statement has been terminated.
Summary & Read More
In nutshell, we have learnt how to resolve String or binary data would be truncated error. Using two ways, trace flag 460 on SQL Server 2016 & 2017. On SQL Server 2019 with compatibility level 150, this feature is by default. If you like this article you may read through Tech-Recipes SQL Server archive posts to learn more.
