Jul 20, 2005

DDL scripts and Transaction Control

Maintaining blog is a serious task. After around two months, I am again writing :-)

Today I got a chance to draft a template for DDL scripts.
DDL scripts are often useful to take the DB structure offline. It is used in installations, source control and documentation
As you all know some of the DDL statements like CREATE TABLE demands them to be the first statement of the batch. Because of this reason, DBAs often include "GO" statement after each DDL script.
What about transaction management is those scripts? Even though BEGIN TRAN... COMMIT TRAN pair will work fine with the GO statement, variable declarations, GOTO statements cannot be separated by GO statement. That means you can't declare a variable in the top of the script and use them in the middle (if you have a GO statement in between!). Worried.... There is more to this
When you have multiple statements and you want to rollback all if error occurs in the middle. You can't use GOTO, variable declaration in the middle of the script.
What Red Gate does is a cool thing: Create a temporary table and send the errors into it.

Nothing much, some simple code, and it works nicely.
Interesting...? Mail me if you like to have a look on that piece of code.