T-SQL truncation error on SQL Server 2005 - bug?

Posted on 2008-11-19
Last Modified: 2012-05-05
Hi everyone,

In migrating from SQL 2000 to 2005 (64 bit) we have come across an insert statement that no longer works.   We have been able to get the original insertion statement to work by leaving one column out and processing it afterwards.  

1. If the (previously ok) problem column is included the insert statement returns "String or binary data would be truncated".
2. If the column is processed on its own the insert statement works correctly.
3. If the column is excluded the insert statement works correctly.
As a work around we use approach (3) and update the table with the missing column - the update works without any problem.

The target table problem column is varchar(100).   The corresponding source column is varchar(500).   None of the entries in the source column is wider than about 50 characters.

The target table has no keys, constraints, triggers or indexes defined.   ANSI settings appear to be the same in 2000 and 2005.

Anyone come across this and any known work arounds?
Question by:MrLogin
    LVL 15

    Expert Comment

    Can you post the INSERT Statement?
    LVL 1

    Author Comment

    Insert into Target_Table
          (Col1, Col2, ID , Name, Amount1, Amount2, Col8)
    select 'XXX', a.Sourcecol1, convert(int,substring( b.SourceCol1, 17, 4)),
                substring( b. SourceCol1, 22, len( b. SourceCol1)),
                (a.Amt11 + a.Amt12 + a.Amt13 + a.Amt14),
                (a.Amt21 + a.Amt22 + a.Amt23 + a.Amt24, Ltrim(a.SouceCol2)
          from Source_Table1 a, Source_Table2 b
          where left( a. Sourcecol1, 4) = substring( b. SourceCol1, 17, 4)

    Of interest we also changed the width of the problem target column to varchar(500) - so it matched the width of the source column exactly and still got the same error.
    LVL 1

    Author Comment

    The problem column is      substring( b. SourceCol1, 22, len( b. SourceCol1))
    LVL 1

    Accepted Solution

    As there is no material response here and a work around is in place to this apparent 64 bit MS SQL server TSQL failure closing the question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now