Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]

  • e.s.brinkman

    SSC Journeyman

    Points: 91

    I had the same problem in Query Analyzer and with the Import Data Wizard. For me the problem was Unicode characters in a text field. After I changed the data type from varchar to nvarchar the problem was gone. Misleading error message from SQL Server. I was looking extensively for the length of the values but in the end it turned out to be a data type issue.

  • dotnetstudy83

    Valued Member

    Points: 65

    I was also getting same kind of error while inserting into table for removing that error I had increased length of one of the column of table

  • Scott-144766

    SSCarpal Tunnel

    Points: 4215

    Same issue here. I did notice that my insert query wasn't actually returning any rows, so changed the job from

    INSERT INTO Table (Field1, Field2, Field3)

    SELECT a.F1, a.F2, a.F3 FROM Table2

    to

    IF EXISTS (SELECT a.F1, a.F2, a.F3 FROM Table2)

    INSERT INTO Table (Field1, Field2, Field3)

    SELECT a.F1, a.F2, a.F3 FROM Table2

    which did the trick for me.

    --
    Scott

  • Rao.V

    Hall of Fame

    Points: 3452

    hi.... alll

    how i can find the entry which is ofover size than my table's column datatype....

    its reallly hard to find out manually as i have more than 60k records in table....

    need a way ASAP.....

  • Ross McMicken

    SSCarpal Tunnel

    Points: 4372

    There's no real easy way to find the data that's too long. I usually look at each cloumn from the source and make sure that the receiver columns are appropriately sized. The next step is to do a select max(len(column_name)) from each column to find the longest item in that column. Once yo ufind the column that has long data, then you can use the result to find the bad data. This works for data coming from other sources as well - I've used the technique in Excel a number of times.

  • Roger Lin-302583

    SSC Journeyman

    Points: 80

    If you don't care the data to be truncated, just turn the ANSI_WARINGS OFF, the data will be truncated to fit the row and no error no fuss everything will work fine. Here is what you can see the effect:

    CREATE TABLE #tst (t char(2))

    insert #tst values ('abc')

    Execute that you get the following error:

    Msg 8152, Level 16, State 14, Line 1

    String or binary data would be truncated.

    The statement has been terminated.

    Then run this:

    set ansi_warnings off

    insert #tst values ('abc')

    You will get this:

    (1 row(s) affected)

  • peter.roothans

    SSC-Addicted

    Points: 425

    Hi,

    I just had the same issue and it was caused by a trigger that stores all updates in an audit table. The audit table stores some additional information as SUSER_SNAME and APP_NAME ...

    After adding substring function the problem was solved

    SUBSTRING(APP_NAME(), 1, 50)

    APP_NAME() in ssms is about 47 characters but when you execute via a job it returns SQL AGENT .... + a guid ... which is longer than 50 chars.

    Kind regards

    Peter.

  • bobaer

    SSC Enthusiast

    Points: 101

    Stuart Mark Housden (9/8/2008)


    I had this same problem where I always get the "truncation" message when executing stored procedures in SQL Server Agent jobs but never getting the message when I run it in Management Studio.

    After trying a number of desperate measures I commented out some "Print" statements I had and the error messages went away.

    This was my problem. Thanks!

Viewing 8 posts - 16 through 23 (of 23 total)

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