CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Siraj Samsudeen
Siraj Samsudeen

Reputation: 1690

Script for running DDL statements in a transaction

I am trying to execute a bunch of DDL statements in a transaction scope. I am trying to execute all the statements that relate to a model change request as as a single transaction so that all the DDL statements fail or succeed together. My objective is not to leave the DB in an inconsistent statement after the execution of the group of DDL statements.

I have found that SQL Server 2008 R2 supports transactions on DDL statements. I am not talking about DROP DB kind of DDL statements - I am referring to CREATE TABLE, ALTER TABLE, DROP TABLE, etc.

I have read the following related threads but did not find an answer.

Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

Unit testing DDL statements that need to be in a transaction

DDL scripts in a transaction block takes effect even when there are errors

What I need is a template script for executing a set of DDL statements as a transaction and to roll them back if one of the statements fail and I want the error to be printed or to be stored in an error table. Can anyone help?

In my research, I have found multiple alternatives, but I am not sure which one to pick as I am new in this area. I need some help from experienced hands.

  1. Use XACT_ABORT in the transaction scope to abort on first error within the transaction
  2. Use a TRY and CATCH block and put the DDL statements inside a transaction inside TRY block

Here are the pages that I have read through.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

http://msdn.microsoft.com/en-us/library/ms188792.aspx

http://www.codeproject.com/KB/database/sqlservertransactions.aspx

Upvotes: 1

Views: 1497

Answers (1)

gbn
gbn

Reputation: 432421

Download Red Gate SQL Compare and see how the scripts are generated there.

This does transactional DDL and can be extended for logging.

TRY/CATCH doesn't span batches which makes is trickier to use without dynamic SQL

Upvotes: 1

Related Questions