Post reply

String or binary data would be truncated

  • May 2, 2009 at 8:38 am

    #72736

    String or binary data would be truncated.

    The statement has been terminated.

    Is there an easy way to know for which column the problem is coming,in case of insert, and for which row value (or string)"

    I have 181 Columns having varchar type with more then 18000 Rows..

    Now I added 50 more columns to this table and create a new table

    I am running query

    insert into table2( < 181 column names> )

    select <181 Column Names > from Table1

    I am getting this error How can I get the Specific Column Name for which column the problem is coming

  • May 2, 2009 at 9:10 am

    #987720

    not an easy way...unless your "Table1" has columns named the same as "Table2"

    you can maybe find something quickly with this sql:

    you can ignore a lot of you 181 columns that are NOT char/varchar, since they cannot raise that truncated error:

    select object_name(id) as tablename,name as columnname, type_name (xtype) as Datatype, Length as datasize from syscolumns

    where object_name(id) = 'Table2'

    and type_name (xtype) IN('char','varchar','nchar','nvarchar')

    then instead of your SELECT statement that is using the insert, change them all to be datalength(colname) and see which one exceeds the max value from the first...

    maybe a macro to build a bunch of statements that do this:

    select datalength(columnname) , from table1 where datalength(columnname) > [datasize from above for the matching column]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 5, 2009 at 5:09 pm

    #989272

    As Lowell suggests, if the column names are the same in your two tables, you can use the syscolumns information to find the offending column. Recognize that although it's DATA from the older table that would be truncated in the new table, that's only because the column defintitions differ.

    This code adapts Lowell's query to examine two tables and find columns whose names match but whose datatype or length differ. Note that it doesn't work with #temp tables as their tablenames are not stored the same way as "permanent" tables.

    create table TestLengthError (

    CharField char(20)

    ,VarCharField varchar(20)

    ,IntField bigint

    ,CharField2 char(20) NULL

    )

    create table TestLengthError2 (

    CharField char(20)

    ,VarCharField varchar(20)

    ,IntField int

    ,CharField2 char(10)

    )

    select object_name(t1.id) as t1_tablename,t1.name as t1_columnname, type_name(t1.xtype) as t1_Datatype, t1.Length as t1_datasize

    ,object_name(t2.id) as t2_tablename,t2.name as t2_columnname, type_name(t2.xtype) as t2_Datatype, t2.Length as t2_datasize

    from syscolumns t1

    join syscolumns t2 on t2.name=t1.name

    where object_name(t1.id) = 'TestLengthError'

    and object_name(t2.id) = 'TestLengthError2'

    and (type_name(t1.xtype) != type_name(t2.xtype)

    or t1.length != t2.length)

    drop table TestLengthError

    drop table TestLengthError2

  • May 6, 2009 at 12:49 am

    #989352

    Column Name and Datatype both are same in Both table.. Previously I took Varchar(20) for each column..and now change some column(appx 120 Columns) length but Now I just want to know the column Name while running the Insert Query

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply