469,904 Members | 2,347 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

ROLLBACK ALTER column statement

Hello,

I'm having trouble using a transaction - rollback statement in sql. I can't seem to have the rollback executed during an error when i've altered a table to add a column with the same name (run the alter script twice on purpose to test the rollback). Somehow i got an error of the column should be unique (since the column already added during the 1st execution of the script). I want the sql execution to display the Failed instead and rollback the transaction. My code is as follows:

Expand|Select|Wrap|Line Numbers
  1. BEGIN TRANSACTION
  2.  
  3. ALTER TABLE dbo.tbl_name
  4. ADD  [column1] [varchar] (20) NULL 
  5. -----------------------------------------------
  6.  
  7. --Error handling
  8. IF @@Error = 0  
  9.     Begin
  10.         COMMIT TRANSACTION
  11.         print '-Success'   
  12.     END
  13. ELSE
  14.     Begin
  15.         ROLLBACK TRANSACTION
  16.         print '-Failed'
  17.     End
  18.  
  19. GO
  20.  
  21.  
Thanks in advance for the help!
Apr 2 '08 #1
2 9966
ck9663
2,878 Expert 2GB
Try putting the t-sql you want monitored in a dynamic query.

Something like:
Expand|Select|Wrap|Line Numbers
  1. BEGIN TRANS
  2. set @sqlStatement = 'ALTER SOMETHING'
  3.  
  4. exec (@sqlStatement)
  5.  
  6. if @@error <> 0
  7.    ROLLBACK
  8. else 
  9.   COMMIT

Also, there's nothing to rollback since the command did not execute. You might also want to check which error can be trapped in relation to it's severity.

-- CK
Apr 2 '08 #2
Thank you for the help on this!
Apr 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
7 posts views Thread by Serge Rielau | last post: by
3 posts views Thread by Gregor Kovańć | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.