Brian Nalewajek - 2007-02-07


  For those generating DDL scripts for physical implementation using SQL Server 2005, I'd like to make a couple of suggestions.

  As there are still issues with the DDL generation for this DBMS, running the CREATE SCHEMA portion of the script as a transaction helps avoid a lot of partial execution problems and cleanup issues.

  The current DDL generation scheme uses GO commands after each statement in the script.  Removing these GO directives form the first part of the script lets the CREATE SCHEMA and CREATE TABLE statments run as an implied transaction (within the CREATE SCHEMA).  Leave only one GO directive after the last CREATE TABLE statement, to commit the inplied CREATE SCHEMA transaction.  This way, if the transaction fails, there are no schema, tables, SPs, etc.... to clean up before ammending and retrying execution of the script.

  I'd futher advise the inclusion of a USE (database name) and GO command at the top of the script, to remove the possibility of running the script against the wrong DB - like the MASTER DB.

  Hope these suggestions make your testing easier - they have mine.  BRN..