How to enable Transaction for DDL on SQL Server

Question

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

If I have 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

Transcation is not working. It is still on transaction one statement. For example, if statement 1 failed, statement 2 still is done when running the script.

How to enable Transaction for DDL?

score 2 · Answer 1 · answered Oct 19 '11 at 15:50

2

You're running the DDL in separate batches so if your first statement raises anything less than a connection-terminating error (hardware problem etc) the second batch will run.

Management studio treats GO as a batch separator and runs each batch separately.

You could use SET XACT_ABORT ON to automatically rollback your transaction in the event of an error. You can also remove the GO statements as ALTER TABLE statements do not need to be run in separate batches.

answered Oct 19 '11 at 15:50

Code Magician

23,217
7
60
77

3

`XACT_ABORT` won't work without also removing `GO` as it will roll back the transaction on error then execute all the rest of the batches outside of an explicit transaction. – Martin Smith Oct 19 '11 at 15:53
Correct, he'll need to follow both steps to get the behavior he wants. Most ALTER errors are not batch terminating, `XACT_ABORT` will change that. – Code Magician Oct 19 '11 at 15:58

score 0 · Answer 2 · answered Oct 19 '11 at 15:56

MagicMike is right, but I implemented an other solution I know to be efficient (even if his solution seems more elegant). FYI, my solution with two transactions and a clean error management (the @@error feature exists on SQL Server, check the equivalent on your SQL, in Oracle it should be something like "exception 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

This particular example splits each alter into its own transaction. I believe you can put your subsequent alter in the IF block to make sure no error has occurred up to that point and move the commit/rollback to a final block like is shown. — oglester, May 18 '21 at 20:49

score 0 · Answer 3 · edited Dec 18 '11 at 14:47

You don't need to disable or enable the DDL command

Just do the following you can use

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

in your terms of example you can do this way

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

How to enable Transaction for DDL on SQL Server

3 Answers3