Lose data when renaming field

Ben MillsBen Mills Posts: 50
edited October 17, 2011 2:17PM in SQL Compare Previous Versions
I changed the name of a SmallInt field in my development database. When I ran a compare (using version 6.1.0.53), the UI matched the fields. I presumed this was just based on the fact that is saw 2 fields in the same position of the same type.

The problem is that when I ran the sync, I lost all the data in the field. I had presumed that if the fields were matched in the UI, then the field would just be renamed and I'd keep all the data.

It's not a big deal in this case, but I'm nervous about using SQL Compare to rename a column in the future.

Is this a bug or by design?
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Hi Ben,

    Sorry to hear you're having issues. In what way did the UI match the fields? I expect that in this case SQL Compare would have identified the mismatch as a missing/additional difference. Since there is no way of 'mapping' columns in SQL Compare, it will always drop one column and recreate another. After all, SQL Compare can't second guess whether you've renamed a column, or whether you've deleted an unwanted and added a new one.

    SQL Refactor is the right tool to rename a column whilst preserving its data.

    http://www.red-gate.com/products/SQL_Refactor/index.htm

    Please let us know if this works for you.

    Thanks and regards,

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    I just ran a test and the UI clearly shows the renamed field on the same line with an arrow between them. I have a screenshot, but I don't see a way of uploading it. Is there an email address I can send it to.

    I realize that SQL Compare would have to guess, but I had presumed it had made an assumption based on the field position and data type.

    Thanks,
    Ben
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    Didn't there used to be a mapping piece to SQL Compare where you could map fields? Maybe a few versions ago?

    Ben
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Hi,

    If you email the screenshot to me, I'll put it into our bug tracking system. There are some known peculiarities in the side-by-side textual comparison control, so this is most likely to be what's causing the confusion.

    My email is david dot atkinson at red-gate.com

    I may be wrong, but I'm not aware that column-mapping has ever been in SQL Compare. You may be thinking of SQL Data Compare, which has had this feature since v5.

    Regards,
    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    I just sent that screenshot.

    It's an interesting deficiency in SQL Compare. I was just reading about data migrations in Ruby on Rails. You can create little scripts that rename fields and tables. I think overall SQL Compare is a lot easier to use, but the ability to map fields would be a great additional feature.

    I looked at SQL Refactor. It looks like it would be awkward to rename fields on a development machine and make those same changes to my staging or production server. I have a very smooth process for using SQL Compare to push schema changes from my development machine to my production server. I'm not sure that SQL Refactor fits in to that process.
    · Share on Twitter
  • Andras BAndras B Posts: 249
    Ben Mills wrote:
    Didn't there used to be a mapping piece to SQL Compare where you could map fields? Maybe a few versions ago?

    Ben

    Hi Ben,

    SQL Compare does not allow mapping columns. We have been looking into this feature, but it has not made it into the releases. Problems usually arise bacause of computed columns, constraints, forign keys, triggers, etc. and these cannot always be reliably refactored on demand either for comparison of for synchronization purposes.
    However, SQL Data Compare supports column mapping, as the above problems are not present during data comparison.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
    · Share on Twitter
  • KieranDonoghueKieranDonoghue Posts: 1
    Hi there - I appear to have the exact opposite problem. I have a column on a table which was reused rather than renamed. What occurred is that a column was no longer required (AllowedFunctionID) but a new column (AllowedFunctionModeLinkID) had the same characteristics (Int) so this column was reused. This old column had a FK constraint on one table (AllowedFunction) which obviously existed in the target database but the new column had a FK to a new table (AllowedModeFunction). When this was being synchronised it tried to take the data from the old column and insert it into the new column. This caused a problem as it would not pass FK constraints as there was no data on the new table. At first I thought this was behaving correctly as it is probably better to try and conserve data rather than delete it but I then saw this thread and did some more investigating. I renamed some other columns and deleted and set up some columns and these dropped the data when synchronised. These had no FK or other constraints. This seems to be inconsistent. I attach the Insert line from the synchronisation script which shows the data being moved from AllowedFunctionID to AllowedFunctionModeLinkID :[INSERT INTO [dbo].[tmp_rg_xx_cmasUserPermission]([UserPermissionID], [RoleID], [AllowedFunctionModeLinkID], [EnteredBy], [EnteredDate], [LastUpdatedBy], [LastUpdatedDate], [CheckedBy], [CheckedDate], [AuthorisedBy], [AuthorisedDate]) SELECT [UserPermissionID], [RoleID], [AllowedFunctionID], [EnteredBy], [EnteredDate], [LastUpdatedBy], [LastUpdatedDate], [CheckedBy], [CheckedDate], [AuthorisedBy], [AuthorisedDate] FROM [dbo].[cmasUserPermission]] If I move the position of the column it will drop the data.[/quote]
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Kieran,

    I rather expected this. SQL Compare tries to preserve data, because around version 3.1 there were a lot of complaints about lost data when columns are renamed and SQL Compare needs to rebuild the table.

    It's not entirely consistent, though. SQL Compare checks for similarly-named columns between the tables being migrated first, and if it finds some, it attempts to copy the data from the 'old' to the 'new' column. From the supplied information, I'd say that since the first bit of the two column names are so similar, SQL Compare plays it safe and transfers the data as part of the schema migration.
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    Hi Brian,

    So SQL Compare sometimes matches columns, but the matching shown in the UI is not necessarily the same as the matching in the synchronization. That seems like a big bug to me.

    Ben
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Ben,

    The renderer used to display the text comparisons and the one that does the internal work are totally different. There are some limitations in the text comparison -- particularly if the columns in a table are ordinally very far apart.
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    Can you give us some rules about when fields will match both in the UI and in the sync?

    This isn't a huge issue as I don't rename fields too often, but I'd like to know that this has been acknowledged as a bug and will be fixed in a future release. My vote is to either add a complete field matching feature or to eliminate the matching completely.

    Thanks,
    Ben
    · Share on Twitter
  • Ben MillsBen Mills Posts: 50
    I have another quick question. If I change just the data type of a field (say VarChar to NVarChar) will SQL Compare make the type adjustment and keep the data?

    The UI indicates that the fields match, but I'm a little nervous that I'm going to lose my data.

    Thanks,
    Ben
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Ben,

    SQL Server will CAST similar datatypes automatically, following the rules that you find in Books Online. If, for instance, a varchar column is converted to a numeric type, the conversion will succeed if the data in the varchar column can be cast as a number. If any alpha data is present in the column, the conversion will fail and the transaction will roll back.

    Text data such as varchar and nvarchar shouldn't be a problem at all unless perhaps the column collations are different. But in any case the SQL should roll back in case of error and place the database back to its' original state.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    SQL Compare can now map columns and tables as part of the tool. It also can work alongside SQL Source Control allowing you to insert your own custom script to override default behavior. For example, you can create an sp_rename script, commit this to source control and SQL Compare will know to reuse this.

    Please visit the following announcement for more information:

    http://www.red-gate.com/MessageBoard/vi ... 1312#51312

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.