By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

ROLLBACK ALTER column statement

P: 10
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
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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

P: 10
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.