Options
Schema change not recognized on "Refresh"
PDinCA
Posts: 642 Silver 1
Summary of schema changes:
1. Added parent table, "Site", to handle multiple websites contributing data to an existing table.
2. Added fk, SiteID, to "Donation" table, NOT NULL.
Data:
1. Added 4 rows to the "Site" table.
2. Filled the Donation.SiteID with ID 1 because there's currently only 1 site providing data.
All this was on the DEV box.
Used SQL Compare to update the schema on our Staging box. Interestingly enough, the generated script failed because adding the fk as NOT NULL without data values in the modified rows isn't kosher. Splitting the script into two and modifying the copy-to-temp INSERT statement got round that.
HOWEVER, I had already opened a session of SQL Data Compare and copied the Site table data across. The Donation table was in the set of tables to compare, because I knew I'd likely run the data sync in 2 stages.
After the 2nd schema change, to the Donation table, using SQL compare's modified script, I went back to SQL Data Compare to complete the data sync.
Problem:
The SiteID, although originally present in only the DEV table and "now" in the Staging table, does not show up, even after:
1. Repeated (frustrated, "I don't believe it") use of "Refresh".
2. Editing the Project in an attempt to get it to recognise the schema change.
I couldn't find a "refresh schema" option or command.
It appears I have to exit the tool and fire it up again... or did I miss something?
1. Added parent table, "Site", to handle multiple websites contributing data to an existing table.
2. Added fk, SiteID, to "Donation" table, NOT NULL.
Data:
1. Added 4 rows to the "Site" table.
2. Filled the Donation.SiteID with ID 1 because there's currently only 1 site providing data.
All this was on the DEV box.
Used SQL Compare to update the schema on our Staging box. Interestingly enough, the generated script failed because adding the fk as NOT NULL without data values in the modified rows isn't kosher. Splitting the script into two and modifying the copy-to-temp INSERT statement got round that.
HOWEVER, I had already opened a session of SQL Data Compare and copied the Site table data across. The Donation table was in the set of tables to compare, because I knew I'd likely run the data sync in 2 stages.
After the 2nd schema change, to the Donation table, using SQL compare's modified script, I went back to SQL Data Compare to complete the data sync.
Problem:
The SiteID, although originally present in only the DEV table and "now" in the Staging table, does not show up, even after:
1. Repeated (frustrated, "I don't believe it") use of "Refresh".
2. Editing the Project in an attempt to get it to recognise the schema change.
I couldn't find a "refresh schema" option or command.
It appears I have to exit the tool and fire it up again... or did I miss something?
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
Decide wisely...
This discussion has been closed.
Comments
There is a minor annoyance with Data Compare that you've unfortunately run into. Data Compare will remember the last settings of a comparison. If you then go in and add a column to a database, Data Compare will definitely pick up the schema change. The trouble is, it will not 'select' that new column.
In other words, it doesn't select the new column because you didn't compare it the last time, which is admittedly a bit of twisted logic. You'd either need to make yourself aware of this and make sure that all of the columns are chosen when you compare data again by looking in the 'columns' column of the second screen of the wizard.
Alternatively, I think it would work just as well to point Data Compare at another set of databases, maybe set of Northwinds, do a quick compare, and then go back and do the databases that you actually want to compare.
We'll definitely be looking towards fixing this in the next major version.