Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

raiserror events and transactions

Posted on 2004-11-02
5
Medium Priority
?
1,087 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:hesaigo999ca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:wael_tahon
ID: 12474413
no you must roll back


IF ( @Condition= 1)
BEGIN
   RAISERROR (Error message', 16, 1)
   ROLLBACK TRANSACTION
END
0
 

Author Comment

by:hesaigo999ca
ID: 12475822
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?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12483103
" 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....
0
 

Author Comment

by:hesaigo999ca
ID: 12488133
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
0
 
LVL 34

Accepted Solution

by:
arbert earned 750 total points
ID: 12489041
Implicit, but not the whole statement.  As soon as the GO is hit, the transaction above will be committed.....

Can you post your script?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question