Better support for transactions and error handling in migration scripts
The migration scripts that are generated don't fit in well to the other code that SQL Compare generates to update a database. In general, the SQL Compare script will have the script that SQL Compare generated, followed by the migration script, and then more SQL Compare generated script. SQL Compare will wrap its sections with BEGIN TRAN and COMMIT TRAN, and have lots of error handling, but the migration script has no error handling and is not part of the larger transactions. This can cause problems where the scripts succeed partially and it can be hard to recover from that.
I'd like to see the migration script have transaction and error handling support in them so that the final SQL Compare script could all run in one transaction. I realize that this is hard because the migration script can be arbitrary SQL code, but I'm hoping that you have some good ideas.
Wesley Smith
shared this idea
-
Wesley Smith
commented
David,
SQL Source control generates code that checks @@ERROR after each statement and inserts into #tmpErrors when an error occurs. This is then used at the end of the script to decide to COMMIT the transaction.
The migration script doesn't have that code after each statement. So, if some operation of the migration script were to fail, the SQL Source control doesn't know about the error and will just continue to the end of the script, where it will commit the changes. I'd like some way for the script to detect an error in the migration script and not commit.
-
@Wesley - I'm not sure I understand how a partial success could occur. If the script fails due to something in the migration script, the whole deployment script will roll back as everything is contained in the high level transaction. Would you be able to describe an example of where this goes wrong?
