Solved

raiserror events and transactions

Posted on 2004-11-02
1,075 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
Question by:hesaigo999ca
    5 Comments
     
    LVL 7

    Expert Comment

    by:wael_tahon
    no you must roll back


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

    Author Comment

    by:hesaigo999ca
    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
    " 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
    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:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    SQL Server Side Trace is a technique of Profiling SQL Server Events Silently (i.e without Using the Profiling Tool). Running a visual tool in production increases overhead, but we can develop server side Trace using Sql Server Profiler itself. We…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    857 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now