How to combine SQL and Data scripts into single transaction

mdp2176

We need to be able to create upgrade scripts that change both schema and data. The data changes are dependent on the schema changes, and the schema changes cannot be applied without the data changes following, or our software will break.

My question is - since the two products (SQLCompare and DataCompare) generate seperate, isolated scripts, how can we combine them into a single script that is transactional? The only way we have found to do this effectively is to combine them and then manually remove the transaction SQL from the DataCompare script and place the following SQL after EVERY statement in the DataCompare script.

@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

This is fine for 10 or so lines, but our DataCompare scripts are hundreds of lines long. There HAS to be an easier way!

mdp2176

I talked to red-gate support, and it appears that there is no way to do this gracefully with any of their products. They suggested that we create three scripts:

1. A SQL Compare script that applies the schema changes.
2. A SQL Data Compare script that applies the data changes if 1 succeeds.
3. A SQL Compare script that rolls back the schema changes if 2 failes.

Of course, if script #1 drops a column then the data in the column is lost if script #3 needs to run. Looks like there is no way to do this with a single script. We have to run the SQL Compare script, and then run the SQL Data Compare script ONLY if the first script had no errors.

Brian Donahue

Hi,

It's been suggested to me that this is the best way to do it:

• Use SQL Compare to create a migration script with transactional 'plumbing'
• Click Synchronize again and change the direction of synchronization. That's your 'rollback' schema script.
• Produce the data synchronization script with Data Compare
• Run the schema migration SQL on the target database
• Run the data migration SQL script on the target database
• If the data script fails, use the schema rollback from step 2 to put the database back in order