SQL Packager is not truly transactional

johnpaulcook

SQL Packager has two separate transactions, one for schema changes, another for data changes. If the schema change transaction succeeds and the data change transaction fails because of a duplicate key error on an insert attempt (which takes effort to figure out because the error status of -1 does not provide useful detail), then the database is left in a very messed up state. Half an update is much worse than no update.

I'm currently manually editing the resx files. I comment out the first COMMIT TRANSACTION and the second BEGIN TRANSACTION. The result is a single transaction for everything. I shouldn't have to do this.

Brian Donahue

Hello,

This is absolutely true. SQL Packager creates two wholly separate scripts and if the SQL succeeds and the data fails, only the data will get rolled back. I don't see this being as much of a problem on new database packages as in upgrade packages because with a new database package you can drop the database and end up where you started easily.

For the time being, you could try a workaround for the upgrade package. If you swap the synchronization direction for the upgrade package, you can save the schema script from inside Packager, that way you should have an 'undo script' that will put the schema right should the data synchronization fail.