CODEWITHSUNDEEP
sqlsql-server
Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

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.

Upvotes: 4

Views: 2876

Answers (2)

Jayanti Lal
Jayanti Lal

Reputation: 1185

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).

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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

Upvotes: 1

Related Questions