Warning: column will be truncated

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Warning: column will be truncated

Postby wayne.mcdaniel » Fri Oct 25, 2013 1:23 am

I'm using SQL Compare 9.0.0.79.

I'm trying to change a column for a memo field from Varchar(8000) to Varchar(MAX). SQL compare generates the script, but gives me a warning: "The column [Notes] on table [table] is being truncated. There may be loss of data."

I did some testing, and this doesn't seem to be the case, but it makes me nervous. Is there any way I lose data going from varchar(8000) to varchar(MAX)??


When I make my upgrade scripts, I always make the rollback script by reversing the comparison. The rollback version - changing from Varchar(MAX) to varchar(8000) gives me no warnings. I'm pretty sure that could lead to data loss - why no warning here?


I found this old post with a similar issue http://www.red-gate.com/messageboard/viewtopic.php?t=6051

It's not suggesting or offering to drop and recreate the table for me, going in either direction. That post suggests the warning was fixed in 6.2 or 7....I wanted to report I'm still getting it in 9. :(
wayne.mcdaniel
 
Posts: 1
Joined: Fri Oct 25, 2013 1:10 am
Location: Jonesboro, Arkansas

Postby eddie davis » Mon Oct 28, 2013 5:23 pm

Hi Wayne

Thank you for you forum post and sorry that you have encountered a problem.

I was able to reproduce the problem using SQL Compare V10.4 and can confirm that the two reported problems are bugs in our software.

In my experience, the data type Varchar(MAX) is used when the number of characters exceeds 8000 characters.

To answer your first question:
I did some testing, and this doesn't seem to be the case, but it makes me nervous. Is there any way I lose data going from varchar(8000) to varchar(MAX)??


In theory, you should not lose any data. However it is always good practise to backup the target database before deploying any changes to it.

To answer your second question:
When I make my upgrade scripts, I always make the rollback script by reversing the comparison. The rollback version - changing from Varchar(MAX) to varchar(8000) gives me no warnings. I'm pretty sure that could lead to data loss - why no warning here?


Yes the data truncation warning should appear as you are correct, moving from Varchar(MAX) to Varchar(8000) could result in a loss of if the data string exceeds 8000 characters before deploying the change.

I suspect that both the reported problems are related and I will need to submit a Bug Report for the SQL Compare Development team to consider. I will provide a further update with the Bug Report Reference Number once I have created and submitted the Bug Report.

Thank you for bringing this issue to our attention.

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby eddie davis » Mon Oct 28, 2013 5:45 pm

Bug Report now submitted, the reference is SC-6610. A similar problem was reported back in 2007 in the V6.1 which was fixed in V6.2.

Please accept my apologies that this bug has reappeared.

I will update this post when I have further information and details of a version of SQL Compare that fixes this issue.

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 942
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests