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?