Wishes for synchronization
HugoKornelis
Posts: 40 Bronze 5
Hi all,
While using SQL Data Compare 6 (version 6.1.1.308), I found some issues with the code generated by the synchronziation wizard.
The scenario: I have some versioned data - so there is a "Version" column as part of the key. Note that these are views, though I expect the same issues to apply when working with base tables.
I wanted to compare the data between two versions, so I set up SQL Data Compare to ignore the Version column, and used filters such as "Version = 1" and "Version = 2" on the two data sources. For the comparison, everything was dandy. But I did run into some issues when trying to synchronize data on some of the tables.
The main problem is that the INSERT statements were generated without including the value for the Version column. Since this column is part of the key, it has to be populated. And as the destination of the synchronization used "Version = 2" as its filter, it makes sense that added rows should have a value of 2 in this column, otherwise they won't really synchronize the two sources (even if the insert statements would not cause errors).
Less important, but still annoying - is there a simple way to exclude specific columns from the synchronization? I guess I could exclude those columns from the comparison alltogether, but what if I do want to compare them, but don't want to include them in the synchronziation? (For instance a LastUpdated column - interesting to know if there are any differences, but set by a trigger so it sohuld not be populated from the synchronization script).
Finally, when I choose not to synchronize directly but launch it in SQL Server Management Studio, it opens a new instance of SSMS instead of just opening a new query tab in my already open instance of SSMS. Did I overlook some option to change this?
While using SQL Data Compare 6 (version 6.1.1.308), I found some issues with the code generated by the synchronziation wizard.
The scenario: I have some versioned data - so there is a "Version" column as part of the key. Note that these are views, though I expect the same issues to apply when working with base tables.
I wanted to compare the data between two versions, so I set up SQL Data Compare to ignore the Version column, and used filters such as "Version = 1" and "Version = 2" on the two data sources. For the comparison, everything was dandy. But I did run into some issues when trying to synchronize data on some of the tables.
The main problem is that the INSERT statements were generated without including the value for the Version column. Since this column is part of the key, it has to be populated. And as the destination of the synchronization used "Version = 2" as its filter, it makes sense that added rows should have a value of 2 in this column, otherwise they won't really synchronize the two sources (even if the insert statements would not cause errors).
Less important, but still annoying - is there a simple way to exclude specific columns from the synchronization? I guess I could exclude those columns from the comparison alltogether, but what if I do want to compare them, but don't want to include them in the synchronziation? (For instance a LastUpdated column - interesting to know if there are any differences, but set by a trigger so it sohuld not be populated from the synchronization script).
Finally, when I choose not to synchronize directly but launch it in SQL Server Management Studio, it opens a new instance of SSMS instead of just opening a new query tab in my already open instance of SSMS. Did I overlook some option to change this?
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Comments
We have already received a request to allow SQL Data Compare to synchronize columns that were not originally included in the comparison (SDC-778), so we will look into that.
SQL Compare and Data Compare launch SSMS and Query Analyzer -- there is no ability to open a new window in an existing instance of a query editor.