Deploying to Scripts Folder

neil.reynolds

Hi,
Due to the way that we deploy schema changes, I need to be able to generate one script file per object (table including foreign keys) and then deploy using an in-house utility.

The in-house utility looks at the script header to determine that order in which the scripts are deployed, it is expecting a DeployOrderID in header string. In SQL Compare when the script files are generated, is it possible to determine the order in which they should deploy? and inject this into the script header? Or at least make it available in some way.

Using SQL compare to deploy to a database from a scripts folder can generate a single deployment script with the objects created in the correct order. Is there a way to surface that information in individual script files?

Hope someone can help.

Cheers
Neil

Mark J

It sounds like an interesting idea, but I don't think this is supported at the moment - scripts folders are intended to be a representation of the database schema that is easy to read / put into source control, but we don't support actually trying to run the SQL contained in a scripts folder, since that's what deployment scripts are for. (As a small but unlikely example, imagine two tables which have FK references to each other - there's no way to correctly order those files to create the tables, but in a deployment script we can pull the table definitions apart and create the FKs after both tables exist)

There might be something we could do around splitting up a deployment script into multiple files that you could feed into your custom tool, but thinking about it there's a lot of work we'd have to do there to make the feature work well in a majority of cases so it's probably not worth it for SQL Compare

David Atkinson

@Neil - it would be useful for us to understand your process better. The scripts folder that SQL Compare reads isn't a collection of files that are designed to be executed. They are just CREATE statements, no more than the definition of the desired schema state. The SQL Compare comparison generates the deployment script when you direct it at a deployment target. Why is it that you require one script per object?

neil.reynolds

Hi,
Thanks for the replies.
It is possible to walk the foreign keys and determine the order in which the scripts need to be applied. I am looking for a tool to help generate the individual files, without having to write it myself.
It does not sound like SQL Compare does what I require, but thanks for your replies.