I have a stored procedure which works most of the time, but every now and again, I get an error message:
Msg 8152, Level 16, State 2, Line 98
String or binary data would be truncated.
The statement has been terminated.
How do I figure out which data string is causing this issue?
For this answer, which handles more complex select queries quite well, let's assume we have three tables defined as follows...
CREATE TABLE [dbo].[Authors](
[AuthorID] [int] NOT NULL,
[AuthorName] [varchar](20) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Books](
[BookID] [int] NOT NULL,
[AuthorID] [int] NOT NULL,
[BookName] [varchar](20) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Publications](
[BookID] [int] NOT NULL,
[PublicationName] [varchar](10) NOT NULL,
[AuthorID] [int] NOT NULL,
[WrittenBy] [varchar](10) NOT NULL
) ON [PRIMARY]
...and we create the following data...
INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 1, 'BOB' )
INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 2, 'JANE' )
INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 3, 'SOREN LONGNAMESSON' )
INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 1, 1, 'My Life' )
INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 2, 2, 'Writing Long Titles For Dummies' )
INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 3, 3, 'Read Me' )
...and our complex query that is throwing the error is...
INSERT INTO Publications SELECT Books.BookID, Books.BookName, Authors.AuthorID, Authors.AuthorName FROM Books JOIN Authors ON Books.AuthorID = Authors.AuthorID
...then we can find the columns that are likely to be offending like this...
Step 1 Convert your INSERT statement into a SELECT INTO statement and write the results to a temporary table like this...
SELECT Books.BookID, Books.BookName, Authors.AuthorID, Authors.AuthorName INTO ##MyResults FROM Books JOIN Authors ON Books.AuthorID = Authors.AuthorID
Step 2 Now execute the following T-SQL to compare the column definitions of your destination table with the source columns of your complex query...
SELECT
SourceColumns.[name] AS SourceColumnName,
SourceColumns.[type] AS SourceColumnType,
SourceColumns.[length] AS SourceColumnLength,
DestinationColumns.[name] AS SourceColumnName,
DestinationColumns.[type] AS SourceColumnType,
DestinationColumns.[length] AS SourceColumnLength
FROM
tempdb.sys.syscolumns SourceColumns
JOIN tempdb.sys.sysobjects SourceTable ON SourceColumns.[id] = SourceTable.[id]
LEFT JOIN sys.syscolumns DestinationColumns ON SourceColumns.colorder = DestinationColumns.colorder
LEFT JOIN sys.sysobjects DestinationTable ON DestinationColumns.[id] = DestinationTable.[id]
WHERE
SourceTable.Name = '##MyResults'
AND DestinationTable.Name = 'Publications'
You can adapt this query to filter down to certain column types (you know the problem is with string or binary data) and also where the length of the source column is greater than the destination columns. Armed with this information you should be left with only a few columns that could possible cause truncation and can start your search from there.
TIP! Check your destination columns for ON INSERT TRIGGERS!!
The issue is clear that one of your column in the table is having a length more than the destination table.
To find the length of the column which might be creating the issue you can run this query
Select Max(Len(Column1)) --Take only varchar columns in this.
, Max(Len(Column2))
, Max(Len(Column3))
From YourTable
Now you can check the length of string with the column length of your destination table. Most probably you will find any one column is having a length more than the specified length your destination table column.
Lets say you get that the column2 has the issue after executing the above query ie the length of your varchar is more than the column length. Then to find the specific value you can run this query:
select * from yourtable
where len(column2)>20 --change 20 to the actual value of your column2
This will print your error message and store incorrect values in a global temp table. It's not ideal and will be applicable in all situations, but it works.
IF OBJECT_ID('dbo.yourTable') IS NOT NULL
DROP TABLE dbo.yourTable;
IF OBJECT_ID('tempdb..#valuesToBeInserted') IS NOT NULL
DROP TABLE #valuesToBeInserted;
CREATE TABLE yourTable
(
ID INT IDENTITY(1,1),
Col1 CHAR(2),
Col2 VARCHAR(5),
Col3 VARCHAR(10)
);
GO
SELECT * INTO #valuesToBeInserted
FROM
(
SELECT '12' col1,'12345' col2,'1234567890' col3 --good value
UNION ALL
SELECT '123','12345','1234567890' --bad value
UNION ALL
SELECT '12','123456','1234567890' --bad value
) A
BEGIN TRY
INSERT INTO yourTable(Col1,col2,col3)
SELECT *
FROM #valuesToBeInserted
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..##TruncatedResults') IS NOT NULL
DROP TABLE ##TruncatedResults;
PRINT ERROR_MESSAGE() + CHAR(13) + 'Truncated values are in ##truncatedResults'
SELECT
CASE
WHEN DATALENGTH(Col1) > 2 THEN 1 ELSE 0
END AS isCol1Truncated,
CASE
WHEN DATALENGTH(Col2) > 5 THEN 1 ELSE 0
END AS isCol2Truncated,
CASE
WHEN DATALENGTH(Col3) > 10 THEN 1 ELSE 0
END AS isCol3Truncated,
* --lazy man's select
--col1,
--col2,
--col3
INTO ##truncatedResults --global temp table
FROM #valuesToBeInserted
WHERE DATALENGTH(Col1) > 2
OR DATALENGTH(Col2) > 5
OR DATALENGTH(Col3) > 10
END CATCH
If you wanted to create a dynamic SQL solution or just don't want to type it out, try this to create your CASE statements and where clause
DECLARE @caseStatement VARCHAR(MAX),
@whereClause VARCHAR(MAX);
SELECT @caseStatement = COALESCE(@caseStatement + ',','') + 'CASE WHEN ' + CONCAT('DATALENGTH(',COLUMN_NAME,') > ',CHARACTER_MAXIMUM_LENGTH) + ' THEN 1 ELSE 0 END AS Is' + COLUMN_NAME + 'Truncated',
@whereClause = COALESCE(@whereClause,'') + CONCAT('DATALENGTH(',COLUMN_NAME,') > ',CHARACTER_MAXIMUM_LENGTH,' OR ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH > 0
AND TABLE_NAME = 'yourTable'
SELECT @whereClause = 'WHERE ' + SUBSTRING(@whereClause,1,LEN(@whereClause) - 3)
SELECT @caseStatement
SELECT @whereClause
Results:
CASE WHEN DATALENGTH(Col1) > 2 THEN 1 ELSE 0 END AS IsCol1Truncated,CASE WHEN DATALENGTH(Col2) > 5 THEN 1 ELSE 0 END AS IsCol2Truncated,CASE WHEN DATALENGTH(Col3) > 10 THEN 1 ELSE 0 END AS
WHERE DATALENGTH(Col1) > 2 OR DATALENGTH(Col2) > 5 OR DATALENGTH(Col3) > 10
A 2016/2017 update will show you the bad value and column.
A new trace flag will swap the old error for a new 2628 error and will print out the column and offending value. Traceflag 460 is available in the latest cumulative update for 2016 and 2017:
More info: https://stackoverflow.com/a/63474873/852208
This error is occurring due to less size for some column, but you are trying to insert more length of Text in to that column.
For Ex:
EMP_Name varchar(10)
and you are trying to insert/update
JOHN VOUGER
during the above case this expections occur. So, first check with the varchar columns and if possible increase the size of the column.
