Adding Custom SQl to Generated SQL

luserant

I am using the toolkit to compare and generate synchronization scripts from our development DB to our production server. The catch is I don't have access to the production DB for comparison, so I use a proxy that is constructed by the previous scripts. Occasionally changes will be made to production in an emergency fix then added to the development DB. This process misses the scripts that I use to setup the proxy to generate the next synchronization scripts, so when I apply the synchronization script to production I get SQL errors because certain objects already exist on production.

All that to say, is there way to add SQL to the generated SQL to check if an object exists when the generated SQL is run?

Brian Donahue

Hello,

You could accomplish this using a standard ADO .NET SqlConnection/SqlCommand to query the database, but I think the best solution is to ship a SQL Compare snapshot file with your project and compare the database against the schema snapshot. That way you don't need to worry about potentially creating objects that already exist or try to alter objects that aren't there.

luserant

Thank you Brian. I think that will work.

Can I use the toolkit to generate and compare a snapshot, or do I have to use SQLCompare?

Thanks again for your help

Brian Donahue

Hello,

There should be an example of saving a snapshot in the SQL Comparison and Synchronization Toolkit help file. Basically, you register a live database using Database.Register() and then make a snapshot with Database.SaveToDisk().

luserant

There doesn't seem to be any way to take a snapshot using data compare. Is is possible to do this?

Brian Donahue

No, sorry.

You may want to have a look at SQL Packager because it can do something similar.