Bug in sql compare for SSC database with linked static data?

mryhmln

I can no longer seem to get sql compare to recognize the linked static data in some look up tables that I am controlling through SQL Source Control. Previously, I recall that SQL Compare would pick up the linked data automatically when running a comparison between my source controlled database and a live database. Now, it's completely ignoring the data, even though it has changed. This is a high priority issue. Please advise.

David Atkinson

SQL Compare has never been able to pick up data. SQL Data Compare is the tool that needs to be used, but until the upcoming release (hopefully due in late February) you'll have to use your source control client to get the scripts folder to a temporary location and use the Scripts Folder option in SQL Data Compare.

We hope to make SQL Compare itself static-data-aware, which would mean that there would be an option to include data at the end of the deployment script, in the same transaction. However, this won't be considered until later this year.

David Atkinson
Product Manager
Red Gate

mryhmln

Because at one point I could swear that it actually worked, but I had to launch SQL Compare from within SSMS on the source controlled database, and then I saw the linked data changes in the comparison output without having to go through SQL Data Compare (which I do remember never actually worked when using a SSC database as the source). So tell me again the best practice for deploying changes to static linked data? What is the purpose of linking it in SSC then? Wouldn't it just be better to write a migration script manually then?

mryhmln

I ran SQL Data Compare using my development database root folder from TFS that was created by SSC when I linked the database to source control as the source scripts folder and I used my staging branch root folder prior to merging from the development branch as the target scripts folder and SDC picked up the differences in the lookup table data. However, when I went to use the SDC synchronization wizard to generate the deployment script, I got an error saying that the "given key was not present in the dictionary". However, if I right-click on just the lookup table object in the comparison pane and select the "Show Object Synchronization Script", I get the script with no errors. If I can use this method to compare dev branch to staging branch as script folders I can probably automate some kind of short term work around to my problem, as long as the synchronization wizard doesn't error out. Otherwise I'm really going to have problems dealing with any static data changes to the database. It will be a maintenance nightmare. As another potential worka round, I tried using migration scripts to manually change the data myself, but I got a message saying that since there were no schema changes involved I couldn't even use a migration script. Please help me figure this out. This is a critical issue for us.