December 2, 2011 at 4:32 am
Grinja (5/26/2011)
Hi,Has anyone got a way to identify which column is to blame when encountering error 'String or binary data would be truncated'?
I've come accross this when attempting to insert a row into a table with over 75 columns and was wondering if it is possible to glean this info out of SQL server somehow.
Thanks for any feedback,
G
I hate that too but always found it the hard way. I did a Google search and both of the solutions are similar but better than nothing:
http://www.youdidwhatwithtsql.com/string-or-binary-data-would-be-truncated/706
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/how-to-find-what-column
December 2, 2011 at 6:39 am
The easy way to find which column is the problem is to change the Insert statement into a Select Into using a temp table as the target, then compare the column max lengths in the actual target and in the temp table.
Insert into dbo.MyTable (columns)
Select columns
from MyDataSource ;
Becomes:
Select columns
into #T
from MyDataSource;
select *
from tempdb.sys.columns as TempCols
full outer join MyDb.sys.columns as RealCols
on TempCols.name = RealCols.name
and TempCols.object_id = Object_ID(N'tempdb..#T')
and RealCols.object_id = Object_ID(N'MyDb.dbo.MyTable)
where TempCols.name is null -- no match for real target name
or RealCols.name is null -- no match for temp target name
or RealCols.system_type_id != TempCols.system_type_id
or RealCols.max_length < TempCols.max_length ;
If you make sure the Select statement columns are aliased to the names of the columns in the target table, you can skip the parts about non-matched columns. (This practice also helps with internal documentation of the script/proc, because it is easy to identify column match-ups. Very useful in debug/refactor.)
This is usually really, really fast and easy when it comes to finding mismatched columns. It will find problem data types (inserting varchar(8000) into nvarchar(4000) for example), or simple truncations.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2011 at 6:46 am
The easy way to find which column is the problem is to change the Insert statement into a Select Into using a temp table as the target, then compare the column max lengths in the actual target and in the temp table.
Excellent tip.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 2, 2011 at 6:54 am
Phil Parkin (12/2/2011)
The easy way to find which column is the problem is to change the Insert statement into a Select Into using a temp table as the target, then compare the column max lengths in the actual target and in the temp table.
Excellent tip.
Why didn't I think of that one! Awesome 😀
December 2, 2011 at 7:15 am
Phil Parkin (12/2/2011)
The easy way to find which column is the problem is to change the Insert statement into a Select Into using a temp table as the target, then compare the column max lengths in the actual target and in the temp table.
Excellent tip.
Oh sure, take something really painful and make it easy. Excellent idea!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2016 at 1:36 pm
Procedure I wrote which does this all for you:
create procedure dbo.fn_tablecompare(@table1 varchar(20), @table2 varchar(20))
as
begin
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + 'select max(len(' + COLUMN_NAME + ')) columnlength, ''' + column_name + ''' columnname from ' + TABLE_NAME + ' union ' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table1
set @sql = LEFT(@sql,LEN(@sql)-5)
set @sql = 'select isnull(columnlength,0) columnlength, b.character_maximum_length, columnname from (' + @sql + ') a join information_schema.columns b on (a.columnname = b.column_name) where b.table_name = ''' + @table2 + ''' and a.columnlength > b.character_maximum_length'
exec (@sql)
end
January 9, 2017 at 6:33 pm
I've used the max(len( approach for years figuring it was good enough. PITA to setup, but OK.
And I've used the commenting/uncommenting blocks of columns to find the problem, also a PITA, but OK.
And, I developed a (testing system only, small to mid sized tables only) Loop to pinpoint rows that cause insert errors.
Tonight I dickered with GSquared's approach. Much better than I could write and it may work in some situations, but it compares importtable1(columnLength) to destinationtable2(columnLength) - It does not help find problem data, row or column.
MetCald's approach worked for my needs in this moment, comparing importtable1(DataLength) to destinationtable2(columnLength). Quickly showed me which column was the problem. And since I could alter the Column size, problem solved.
While his code is far better than I could do on my own, it I did catch a typo (bolded): select max(len(' + COLUMN_NAME + ')) AS columnlength, ''' + column_nam....
For my needs I switched it out of a Stored Procedure, but I am going to consider adding it to my data cleansing kit - Nice code!
Edit: This code below worked on SQL 2014 Standard
DECLARE @table1 varchar(20), @table2 varchar(20);
SET @table1='MasterSlots_new'; --Table with data you want to insert
SET @table2='MasterSlots'; -- Table data will be inserted to
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + 'select max(len(' + COLUMN_NAME + ')) AS columnlength, ''' + column_name + ''' columnname from ' + TABLE_NAME + ' union ' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table1
set @sql = LEFT(@sql,LEN(@sql)-5)
set @sql = 'select isnull(columnlength,0) columnlength, b.character_maximum_length, columnname from (' + @sql +
') a join information_schema.columns b on (a.columnname = b.column_name) where b.table_name = ''' + @table2 + ''' and a.columnlength > b.character_maximum_length'
/* To debug/review, uncomment Print command then copy SQL from the results window, pasting into the SQL area. */
--PRINT @sql RETURN
exec (@sql);
January 9, 2017 at 8:26 pm
ReReplaced (1/9/2017)
Tonight I dickered with GSquared's approach. Much better than I could write and it may work in some situations, but it compares importtable1(columnLength) to destinationtable2(columnLength) - It does not help find problem data, row or column.
Not sure what you did wrong but GSquared's method works just fine. The SELECT INTO creates a new temporary table with all columns of the correct max length for the data and then compares those column lengths with that of the original table, producing a list of the columns by name where the lengths are different. Then it's just a matter of searching the column for anything that completely fills the column by length to isolate the offending data by row.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2017 at 3:42 pm
OK, here's a good one. I import my data into a table with all field defined as VARCHAR. Then I INSERT into a second table with all columns defined as I need the data to be stored with the conversion is happening here. Then I INSERT from the second table into the third table for the data, with columns defined identically to the second table. I get error Msg 8152 String or binary data would be truncated.
HOWEVER, following the reported error, the rowcounts of all three tables are the same.
I've checked the original .CSV input file for invalid characters using notepad++ and it finds no invalid characters.
This thing is driving me nuts!
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
October 15, 2017 at 4:32 pm
skeleton567 - Sunday, October 15, 2017 3:42 PMOK, here's a good one. I import my data into a table with all field defined as VARCHAR. Then I INSERT into a second table with all columns defined as I need the data to be stored with the conversion is happening here. Then I INSERT from the second table into the third table for the data, with columns defined identically to the second table. I get error Msg 8152 String or binary data would be truncated.
HOWEVER, following the reported error, the rowcounts of all three tables are the same.
I've checked the original .CSV input file for invalid characters using notepad++ and it finds no invalid characters.
This thing is driving me nuts!
VARCHAR columns have a length. What length are you using?
At what stage of your process do you get the error?
What makes you think that 'invalid characters' have got anything to do with this error, given that it is about truncation?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 16, 2017 at 6:43 am
skeleton567 - Sunday, October 15, 2017 3:42 PMOK, here's a good one. I import my data into a table with all field defined as VARCHAR. Then I INSERT into a second table with all columns defined as I need the data to be stored with the conversion is happening here. Then I INSERT from the second table into the third table for the data, with columns defined identically to the second table. I get error Msg 8152 String or binary data would be truncated.
HOWEVER, following the reported error, the rowcounts of all three tables are the same.
I've checked the original .CSV input file for invalid characters using notepad++ and it finds no invalid characters.
This thing is driving me nuts!
It probably has nothing at all to do with the CSV file because you successfully loaded from the file to the first table. You even insert from the first table to a second table with no errors. The time you have a problem is when you insert from one table to a third table, correct? You "just" need to do a column comparison for the two tables using sys.columns to see what the differences are.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2017 at 6:53 am
This would actually make a good problem solving interview question.
Assuming you're selecting into a table, one simple approach is to use something like the following to determine the maximum value length for each source column.
SELECT MAX(DATA_LENGTH(Col1)) AS mCol1
, MAX(DATA_LENGTH(Col2)) AS mCol2
, MAX(DATA_LENGTH(Col3)) AS mCol3
FROM TableA;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 16, 2017 at 7:51 am
Well, the problem may be solved. I switched from using SSMS Import Wizard to a single-step SSIS bulk insert and now the same data files, over 20 files of historical data, some overlapping with identical rows, load properly. The whole purpose of the load is to analyze and organize the data. This may be a difference between the two import processes, which I suspect because I have had the same issue with two entirely different sets of unrelated data for different purposes. One set was known to contain foreign language characters which was the reason for cleaning it with Notepad++. The second set was all self-created data using a standard commercial application. The only common process was the change from SSMS Import to SSIS Bulk Load.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
February 16, 2018 at 6:00 am
In my select list I replaced all columns by max(len(<your_column_name> ) ) as <your_column_name>
So, it returns one row with all the max lengths.
Then you can look at your table columns and hopefully see which one is exceeding the length.
p.s. to wrap the max(len etc. around all columns in select statement, I used excel to edit the select list.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply