How to enable transaction for DDL on SQL Server

Question

Possible duplicate:
Is it possible to run multiple DDL statements inside a transaction (in SQL Server)?

If I have the following script:

BEGIN TRAN GO ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_1] GO ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_2] GO COMMIT TRAN 

Transaction does not work. It is still in the transaction for one statement. For example, if instruction 1 is not executed, statement 2 is still executed when the script runs.

How to enable transaction for DDL?

Answer 1

You run DDL in separate batches, so if your first operator picks up something less than a connection termination error (hardware problem, etc.), the second installment starts.

Management Studio sees GO as a batch separator and launches each batch separately.

You can use SET XACT_ABORT ON to automatically roll back a transaction in case of an error. You can also delete GO statements, since ALTER TABLE statements should not be run in separate batches.

Answer 2

MagicMike is right, but I implemented another solution that I know to be effective (even if its solution seems more elegant). FYI, my solution with two transactions and pure error management (the error function @@ exists on SQL Server, check the equivalent on your SQL, in Oracle it should be something like "exceptions when others" instead of "If (error @@) = 0) "):

 begin tran ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_1] IF (@@Error=0) begin COMMIT TRAN end else begin rollback tran END begin tran ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_2] IF (@@Error=0) begin COMMIT TRAN end else begin rollback tran END 

Answer 3

You do not need to disable or activate the DDL command

Just do the following you can use

 Begin Try ....... End Try Begin Catch ....... End Try 

in your examples you can do it

 begin try ALTER TABLE [dbo].temp CHECK CONSTRAINT [FK_1] --GO ALTER TABLE [dbo].temp CHECK CONSTRAINT [FK_2] --GO end try begin catch print 'Error in the Try Block' end catch