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

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
Can you post the INSERT Statement?
MrLoginAuthor Commented:
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.
MrLoginAuthor Commented:
The problem column is      substring( b. SourceCol1, 22, len( b. SourceCol1))
MrLoginAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.