Warning: column will be truncated
wayne.mcdaniel
Posts: 3 New member
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.
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.
Comments
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:
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:
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com