Find the most interesting bug or enhancement request (and it can be your own), and write a blog post about it (including a link to the Connect item so that folks who agree with you can upvote the item)
This one was pretty easy for me, it is the following connect item Please fix the "String or binary data would be truncated" message to give the column name
This error drives me crazy as well, it should be fairly easy to tell me if nothing else what damn column barfed on the data inserted, but no.. all you get is something like
This is like not having the black box after a plane crashed, you know the plane crashed, but you don't know why exactly.
Dealing with this issue on a semi-regular basis, I even have written my own T-SQL helper to quickly see where the issue is
declare @ImportTable varchar(100) declare @DestinationTable varchar(100) select @ImportTable = 'temp' select @DestinationTable = 'TestTrunc' declare @ImportTableCompare varchar(100) declare @DestinationTableCompare varchar(100) select @ImportTableCompare = 'MaxLengths' select @DestinationTableCompare = 'TempTrunc' declare @sql varchar(8000) select @sql = '' select @sql = 'select 0 as _col0 ,' select @sql += 'max(len( ' + column_name+ ')) AS ' + column_name + ',' from information_schema.columns where table_name = @ImportTable and data_type in('varchar','char','nvarchar','nchar') select @sql = left(@sql,len(@sql) -1) select @sql +=' into ' + @ImportTableCompare + ' from ' + @ImportTable --select @sql -debugging so simple, a caveman can do it exec (@sql) select @sql = '' select @sql = 'select 0 as _col0, ' select @sql += '' + convert(varchar(20),character_maximum_length)
+ ' AS ' + column_name + ',' from information_schema.columns where table_name = @DestinationTable and data_type in('varchar','char','nvarchar','nchar') select @sql = left(@sql,len(@sql) -1) select @sql +=' into ' + @DestinationTableCompare --select @sql -debugging so simple, a caveman can do it exec (@sql) select @sql = '' select @sql = 'select ' select @sql += '' + 'case when t.' + column_name + ' > tt.' + column_name + ' then ''truncation'' else ''no truncation'' end as '+ column_name + ',' from information_schema.columns where table_name = @ImportTableCompare and column_name <> '_col0' select @sql = left(@sql,len(@sql) -1) select @sql +=' from ' + @ImportTableCompare + ' t join ' + @DestinationTableCompare + ' tt on t._col0 = tt._col0 ' --select @sql -debugging so simple, a caveman can do it exec (@sql) exec ('drop table ' + @ImportTableCompare+ ',' + @DestinationTableCompare )
Something like this only helps you if you have the data readily available, what if it is from an application? In that case you need profiler or extended events to capture the statement
It is also crazy that this connect item is almost 9 years old, it was opened in April 2008
We do have someone from Microsoft commenting on this issue last August
Posted by David [MSFT] on 8/5/2016 at 1:39 PM
This connect item has 1328 upvotes as of today, it also has 5 downvotes (who are these people..probably working on the SQL Server team :-) )
So there you have it that is my contribution to T-SQL Tuesday # 86, keep track of Brent's blog here https://www.brentozar.com/blog/ there will be a recap posted on Tuesday, January 2017
No comments:
Post a Comment