• Question

  • I have got a try/catch in my stored procedure
    looks like the try part is all done with no error but then it also executes the catch part of the code, I was expecting that part to execute only in case of error.

    Any suggestion?

    Begin Try


     Begin Transaction CHist

     Insert the records

     ----Delete some records ----

     ----All Done----

     COMMIT Transaction CaHist
    End Try
    Begin Catch
     Rollback Transaction CHist
    End Catch

    The error I get is:

    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Saturday, September 19, 2009 4:37 PM


All replies

  • Do you have any sp's inside your sp?
    Abdallah, PMP, MCTS
    Saturday, September 19, 2009 5:40 PM
  • The SP where I am having a problem is called from another SP and also I am calling other sp

    deos that matter?

    Saturday, September 19, 2009 6:59 PM
  • Then there must be an issue in one of the sp's not committing the transaction or an issue with the try catch block.
    I would suggest you review each sp seperately and see where the issue is.Or feel free to post the nested sp's logic and we'll try to help out.

    Abdallah, PMP, MCTS
    Saturday, September 19, 2009 7:02 PM
  • removed the stored procedure call out side the try/catch same issue :(
    Saturday, September 19, 2009 7:48 PM
  • Based on the code above, everything looks fine except the type on the tran name on the commit.
    There must be something wrong with the sp's called ubside your sp.
    You get this error if there is one BEGIN TRAN missing. Even if your data is committed and everything is successful, the ROLLBACK TRAN inside the catch block will still need to have a BEGIN TRAN.

    Abdallah, PMP, MCTS
    Saturday, September 19, 2009 8:06 PM
  • Thanks Abdallah,

    That name difference was a typo on my part in trying to copy the essential part of the code to the forum, so ignore that.

    I added the if @@TRANCOUNT > 0 then rollback thing so it prevents the error I am getting

    Here is the funny part:

    My code is more like follows, see the MsgString, it returns me the value of 'SUCCESS' not 'Failed', this is crazy 

    As I mentioned I have removed the Sub procedures outside my Try/Catch

     Begin Try
      Begin Transaction CHist
      --Do something
      COMMIT Transaction CHist
      Set MsgString= 'SUCCESS'
     End Try
     Begin Catch
      Rollback Transaction CHist
      Set MsgString= 'Failed'
     End Catch




    Saturday, September 19, 2009 8:40 PM
  • if you are trying to roll back  nested transaction then you can  get these kind of errors

    check the following code

    Begin try
    	Begin Transaction CHist
    	begin transaction   
    	select 2
    	Rollback Transaction  
    	raiserror ('Exception:Error occured',11,3,'','','')
    	select 3 
    	commit Transaction CHist
    end try
    begin catch
    select ERROR_MESSAGE(),ERROR_SEVERITY(),error_state();
    select 4 
    Rollback Transaction CHist
    end catch 
    also check the following link may be it can help you
    • Marked as answer by Zongqing Li Friday, September 25, 2009 7:28 AM
    Saturday, September 19, 2009 9:11 PM
  • Why don't you try the following. Move your Begin transaction outside the BEGIN TRY, rollback your transaction inside the catch block if @@TRANCOUNT> 0, and then commit transaction inside the TRY block.
    With that little information about the sp, it's really hard to troubleshoot.And as Arif said, when you have nested sp with transactions, you need to be very careful.

    Abdallah, PMP, MCTS
    Saturday, September 19, 2009 10:47 PM