• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1096
  • Last Modified:

raiserror events and transactions

Does anyone know if when in the middle of a transaction , by using a raiserror with a very high state level , if this cancels the transaction without doing a rollback or what?

I heard that a raiserror with a high state level cancels the running program...thereby theoretically cancelling your transaction in the middle without doing a rollback...is this true, what happens with this as I am using a raiserror to report and stop db processing but want to use a transaction to return the db to its previous state if anything happens....

Thanks all
  • 2
  • 2
1 Solution
no you must roll back

IF ( @Condition= 1)
   RAISERROR (Error message', 16, 1)
hesaigo999caAuthor Commented:
the question though is pertaining to the actual level used for the state level...if you read the
sql documentation...they state that if you use a high enough state level for your raiserror it quits immediately and jumps out of the program running.

I still need to know if there is any chance of this meaning it quits before continuing any other
action of the script,thereby getting out before calling the rollback,or if it just continues in the script, but does not execute the last command before the raiserror was sent?
" no you must roll back"????

If you're in the middle of a transaction and an error occurs (and there wasn't a COMMIT TRAN before the error), the transaction will rollback.  SQL Server will not leave a partial transaction as committed or uncommitted....
hesaigo999caAuthor Commented:
what if a script is being rolled without any trans, just go statements....
does sql server treat this as an implicit transaction, and rollback to the beginning of the script ...or to the last go used.

Reason being, I can not really use a trans at certain points of my script, they need to access committed data for table cloning puproses.

Thanks a million in advance
Implicit, but not the whole statement.  As soon as the GO is hit, the transaction above will be committed.....

Can you post your script?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now