[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2008-11-19
Medium Priority
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
  • 3
LVL 15

Expert Comment

by:Faiga Diegel
ID: 23000311
Can you post the INSERT Statement?

Author Comment

ID: 23000528
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.

Author Comment

ID: 23000966
The problem column is      substring( b. SourceCol1, 22, len( b. SourceCol1))

Accepted Solution

MrLogin earned 0 total points
ID: 23047800
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

825 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