"I am trying to compare two databases and xSQL Data Compare has marked all table pairs with a red X and the messages on the output window show something like [16:08:11] Session [New Compare* (2)]: Pair [dbo].[MyTable] - [dbo].[MyTable] can't be compared. - what am I doing wrong?"
This is more or less a typical email we receive quite often from our users. The answer is, you are not doing anything wrong, but whoever designed that database didn't do a great job!
When comparing two database the xSQL Data Compare first reads the list of tables and views as well as their definitions and performs an automatic pairing of the tables and views based on names. There are certain mapping rules that allow the user to configure the mapping but by default it pairs them based on a simple name match. It then pairs the columns from both tables on each table pair and last but not least, it identifies the candidate comparison keys and decides which one to use. A candidate comparison key must uniquely identify each row on the table, so the ideal comparison key would be the Primary Key of the table but a unique index would be ok too. When it does not find a candidate key for a table pair it marks the pair as "non-comparable" since it has no way to match rows to each other. All tables in your database should have a primary key. However, if you find yourself in charge of a database that does not have primary keys defined don't despair - with a little bit of extra work you can still compare those tables. The xSQL Data Compare allows you to manually select comparison keys for each table pair:
To manually define the comparison keys for a table pair, drill down on that pair (click on the ... button on the left of the pair) and on the window that appears click on the "Unique Keys" tab. Choose one or more columns that you know form a unique key for the table and select that combination of columns as the comparison key. You will need to go through this exercise for all table pairs that are shown as non-comparable so it's a bit tedious however, once you have done this xSQL Data Compare will remember and the next time you need to compare those databases you will not see the can't compare message anymore.
xSQL Data Compare supports all SQL Server versions from SQL Server 2000 to SQL Server 2016 as well as SQL Server Azure, and it's free for SQL Server Express edition. Download now and see for yourself why thousands of users love it!
This is more or less a typical email we receive quite often from our users. The answer is, you are not doing anything wrong, but whoever designed that database didn't do a great job!
When comparing two database the xSQL Data Compare first reads the list of tables and views as well as their definitions and performs an automatic pairing of the tables and views based on names. There are certain mapping rules that allow the user to configure the mapping but by default it pairs them based on a simple name match. It then pairs the columns from both tables on each table pair and last but not least, it identifies the candidate comparison keys and decides which one to use. A candidate comparison key must uniquely identify each row on the table, so the ideal comparison key would be the Primary Key of the table but a unique index would be ok too. When it does not find a candidate key for a table pair it marks the pair as "non-comparable" since it has no way to match rows to each other. All tables in your database should have a primary key. However, if you find yourself in charge of a database that does not have primary keys defined don't despair - with a little bit of extra work you can still compare those tables. The xSQL Data Compare allows you to manually select comparison keys for each table pair:
To manually define the comparison keys for a table pair, drill down on that pair (click on the ... button on the left of the pair) and on the window that appears click on the "Unique Keys" tab. Choose one or more columns that you know form a unique key for the table and select that combination of columns as the comparison key. You will need to go through this exercise for all table pairs that are shown as non-comparable so it's a bit tedious however, once you have done this xSQL Data Compare will remember and the next time you need to compare those databases you will not see the can't compare message anymore.
xSQL Data Compare supports all SQL Server versions from SQL Server 2000 to SQL Server 2016 as well as SQL Server Azure, and it's free for SQL Server Express edition. Download now and see for yourself why thousands of users love it!
Posts
0 comments:
Post a Comment