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
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

arbertConnect With a Mentor Commented:
Implicit, but not the whole statement.  As soon as the GO is hit, the transaction above will be committed.....

Can you post your script?
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
All Courses

From novice to tech pro — start learning today.