Warning: column will be truncated

wayne.mcdaniel

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 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.

Eddie D

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 D

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