Identify Which column cause String or binary data would be truncated. : SQL server

Question

I have application which will insert to multiple table with single click. these table has nearly 100 - 150 columns.

It's working fine but sometime anyone of the column makes the error String or binary data would be truncated. I know the error cause but i want to know which column in the cause for this issue.

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.

Is it possible to identify in SQL server? Since my application is production side. I am not able to reproduce the error.

How are the inserts done: insert statements built in the app, insert statements inside Stored Procs on the DB side ? — Eduard Uta
– Eduard Uta, Commented Oct 20, 2015 at 7:22
@StanislovasKalašnikovas I am doing my insert from SQL server Sp's. It is norml insert — Vignesh Kumar A
– Vignesh Kumar A, Commented Oct 20, 2015 at 7:27

Jayanti Lal · Accepted Answer · 2018-02-28 11:28:26Z

Import the data into a new table using VARCHAR(MAX) as the datatype for the columns. Then you can use DATALENGTH to get the maximum size of each column.

 SELECT MAX(DATALENGTH(col1)) AS col1, MAX(DATALENGTH(col2)) AS col2, etc. FROM newTable

This will tell you which column(s) exceed the size of your column(s).

Rahul Tripathi · Accepted Answer · 2015-10-20 07:24:35Z

You can check this MSDN blog:

declare @table_name varchar(200) set @table_name = 'Branch' declare @max_length_default int, @column varchar(50), @max_length int, @max_length_string varchar(10) create table #temp_table (column_name varchar(50), max_length int, max_length_default int) declare @column_id int, @max_column_id int select @column_id = min(b.column_id), @max_column_id = max(b.column_id) from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 ) -- select @column_id, @max_column_id declare @command varchar(2000) while(@column_id <= @max_column_id) begin set @column = null select @column = b.name, @max_length_default = b.max_length from sys.tables a, sys.columns b where a.[name] = @table_name and a.object_id = b.object_id --and b.system_type_id in ( 231, 167 ) and b.column_id = @column_id --select @column, @max_length_default if( @column is not null ) begin set @command = 'insert into #temp_table(column_name, max_length, max_length_default) select ''' + @column + ''', max(len(cast([' + @column + ']as varchar(8000)))),' + cast(@max_length_default as varchar(5)) + ' from ' + @table_name + ' where [' + @column + '] is not null' --select @command exec(@command) end set @column_id = @column_id + 1 end select * from #temp_table drop table #temp_table

Can you please explain this answer? what is difference in max_length and max_length_default ?
@VigneshKumar:- max_length is the maximum length of the value which is stored in the column and max_length_default is the value which you have provided while creating the column ie., the datatype length.
@VigneshKumar:- For example you create a table with column A as varchar(100) and then insert the value in this column as 'ABCDEF' then max_length is 6 and max_length_default is 100

Collectives™ on Stack Overflow

Identify Which column cause String or binary data would be truncated. : SQL server

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related