Columns in Comparison keep resetting on some (not all) table

tkdennistkdennis Posts: 114
edited August 4, 2008 12:11PM in SQL Data Compare Previous Versions
I have some wide tables where I'm mostly interested in 3-4 columns in the compare. In my project, I select 5 out of 11 tables for comparison, select the Comparison Key, deselect most of the columns in 4 of the selected tables.
Table / Columns in Comparison
A 4 of 22 (1st column key; columns 3, 16, 17 selected)
B 4 of 59 (3rd column key; columns 2, 10, 11 selected)
C 3 of 6 (1st and 2nd column key; column 4 selected)
D 5 of 5 (1st column key; all columns selected)
E 2 of 5 (1st and 2nd column key; no additional columns selected)

Click Save, then click Compare Now. Comparison results look good. Then click Edit Project, Data Sources, change right-side Data Source Server (same database name, same exact schema for those tables), click Tables and View and see this:
A 21 of 22 (1st column key; only column 2 is deselected)
B 57 of 59 (3rd column key; only columns 1 and 4 are deselected)
C 3 of 6 (1st and 2nd column key; column 4 selected)
D 5 of 5 (1st column key; all columns selected)
E 2 of 5 (1st and 2nd column key; no additional columns selected)

Go through and deselect most columns again
A 4 of 22 (1st column key; columns 3, 16, 17 selected)
B 4 of 59 (3rd column key; columns 2, 10, 11 selected)
C 3 of 6 (1st and 2nd column key; column 4 selected)
D 5 of 5 (1st column key; all columns selected)
E 2 of 5 (1st and 2nd column key; no additional columns selected)

click Save, click Compare Now. Project runs a long time, data compare shows most columns are compared! Click Edit Project and see this
A 21 of 22 (1st column key; only column 2 is deselected)
B 57 of 59 (3rd column key; only columns 1 and 4 are deselected)
C 3 of 6 (1st and 2nd column key; column 4 selected)
D 5 of 5 (1st column key; all columns selected)
E 2 of 5 (1st and 2nd column key; no additional columns selected)

Click Cancel, close SQL Data Compare, reopen Saved Project, click Edit, click Tables & Views, columns are still wrong so the Save Project didn't save the modified column selections.

Ideally, the column selections would be retained with the table object and restored with that table even when the server/database is changed, unless, of course, the columns themselves are changed. In addition, if the column selections are changed on Tables & Views, the Save and Compare Now should retain those selections.

I first noticed this in Data Compare 6, but I upgraded to version 7.0.0.559 and it still happens the exact same way.

Thanks,
Traci

Comments

  • Eddie DEddie D Posts: 1,733 Rose Gold 5
    Thank you for your post into the forum and sorry that you have encountered this problem.

    I will fully test the fault condition you have reported and attempt to recreate your fault symptoms.

    In the meantime I do have a workaround for you to try.

    You create the first project, comparing Server1/MyDatabase to Server2/MyDatabase, where Server 2/MyDatabase is the right hand data source.

    Instead of editing this first project, replacing Server2/MyDatabase with Server3/MyDatabase.

    Can you please create a new project to compare Server1/MyDatabase to Server3/Mydatabase.

    I report my findings once I have tested for your fault condition.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Thank you for your reply and workaround. I can create a new project to change the right-hand server and have two projects, but that does require me to go through the whole column selection process again. I am trying to verify data is synchronized between 4 servers with the same column selections. Is there a way to copy the project's column selections from one project to another?

    Thanks,
    Traci
  • Eddie DEddie D Posts: 1,733 Rose Gold 5
    I am unable to recreate the reported problem.

    I have 3 SQL 2005 Instances.
    Created the exact same database schema on all three instances and populated with sample database.

    Set a configuration similar to yours:
    TableA selecting 4 of 22 columns
    TableB selecting 4 of 59 columns
    TableC selecting 2 of 5 columns
    TableD 3 of 6 columns
    TableE selecting all 5 columns.

    When I switch the right hand data source to another SQL Instance my project configuration remains unchanged. I do not get the changes to the Columns in Comparison that you have reported.

    Can you please confirm that each database has the exact same schema and collation?

    When you edit the project configuration, are you Remapping any objects?

    When you select different data sources for the right hand data source, are you using the same version / edition of SQL Server?

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • I can reproduce the problem on 3 identical databases on the same SQL Server with a single table identical across all 3 databases.

    Try using the keyboard to deselect the columns if you haven't already - that may be making the difference.

    1. click on a column to the right of the checkbox
    2. use the down arrow key to highlight a range of columns
    3. press the spacebar to deselect the columns
    4. save the project and look at the sdc file - in my tests, the columns are not in the XML as a Deselect action.
    5. Run the compare - the deselect columns appear in the comparison.
    6. Deselect the columns again (with the keyboard)
    7. Run the compare - this time it looks good.
    8. save the project and look at the sdc file - it still doesn't have the deselect action on the columns
    9. Change data source.
    10. Click Tables & Views
    11. All columns are reselected again.

    If you still are not able to reproduce the problem, I can create a video demonstrating it.

    Thanks,
    Traci

    CREATE TABLE test1 (
    col1 INT,
    col2 INT,
    col3 INT,
    col4 INT,
    col5 INT,
    col6 INT,
    col7 INT,
    col8 INT,
    col9 INT
    )

    select col2 and col3 as key
    use keyboard down arrow and space to highlight and deselect col5-col8 at the same time.
  • Eddie DEddie D Posts: 1,733 Rose Gold 5
    Sorry for the delay in replying.

    Using the keyboard to deselect the columns, I am able to reproduce the problem.

    I have submitted a bug report to highlight the issue to our development team.

    I will be maintaining a 'watch' on the bug report submitted and will keep you updated on its progress.

    Sorry that you have encountered this issue and thank you for bringing it to our attention.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.