Tables get deselected when switching data sources

kevinse

Context
We have two databases that need to be synchronized when a dev makes changes: one for development, and one that represents a stock version of our database to be distributed to customers. Some tables contain a mix of static data (data we intend to ship with the product) and user/dev data, so we only include rows that we consider static by using the where clause feature in Data Compare. The tables that are entirely user data are simply deselected on the tables and views tab of the project modal.

Problem
If I open the Data Compare project file and look at the table mappings, everything is fine. However, as soon as I change one of the data sources from database to scripts folder and choose the appropriate scripts folder, any tables with a where clause get deselected. However, because we have a large assortment of tables and only a subset of them are to be considered in this data comparison, I now have to go and manually reselect all of these tables.

Why are they getting deselected when I change the data source type? The DB schema is identical on both sides of the comparison.

Grant

Each set of databases (or backups, scripts. whatever you're comparing) is a unique set. Changing the targets is telling Compare that you have a new thing for it to look at. We can't know that these two sources are the same after you switch one of them, so it has to reread and reset, just in case they are different.

kevinse

Thanks for the response.

What you're saying makes sense, but... any time I switch between similar databases (without changing the comparison type over to backup, source control, scripts, etc.) it keeps my selections. So apparently it's willing to do what I want (and forego the "reset") sometimes. This inconsistency is misleading; perhaps it should warn the user. It took me a while to notice this was happening.

I'm also now realizing that SQL Compare is also throwing away my where clauses on one side of the comparison, so the problem is larger than having to simply recheck boxes. This is unfortunate.

I guess I'll try to work around the issue by comparing/editing the project XML myself.

Grant

That may be a valid approach for now. Maybe we need a way to keep the selections optionally. Some people would be equally displeased with the behavior if it kept everything by default. Having both might be an approach.

Please look over User Voice and if you can't find this, add it. I'll be happy to vote on it: https://redgate.uservoice.com/forums/147879-sql-data-compare

There is already a request on the WHERE clause: https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/32152780-use-the-same-where-clauses

And, I think this might be similar to what you're asking for: https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/30973708-maintain-custom-table-selections-after-first-datac

If not, please, add yours. We do use these to drive development.