Transaction Error

  • I have the following script that I'm running in query analyzer:

    BEGIN TRAN

    UPDATE JOBS_Openings

    SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)

    IF @@ERROR <> 0 ROLLBACK TRAN

    UPDATE JOBS_Openings

    SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)

    WHERE CHARINDEX('(', AARPrimary) > 0

    IF @@ERROR <> 0 ROLLBACK TRAN

    COMMIT TRAN

    Please note that the first update statement in this transaction purposely generates an error for testing.  However, the second update statement is executed in spite of the ROLLBACK request in the previous update.  In addition, I receive the following error message:

         "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

    Would somebody please explain the problem with my syntax?  Why isn't my transaction rolled back when the first update statement in the transaction generates an error?  Why doesn't my COMMIT TRANS find the corresponding BEGIN TRANS?

    Thanks in advance for any help you can provide!

    Chris

  • its possible the transaction is open from your testing. on that connection. save the query close the connection and start a new one.

    or

    just execute rollback tran until it comes back with that message

    Otherwise, I think it would be okay.

    BEGIN TRAN

    UPDATE JOBS_Openings

    SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)

    IF @@ERROR <> 0

      goto Error

    UPDATE JOBS_Openings

    SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)

    WHERE CHARINDEX('(', AARPrimary) > 0

    IF @@ERROR <> 0

      goto Error

    COMMIT TRAN

    Error:

     begin

        rollback tran

        return

     end

  • Thanks so much for your reply. 

    Per your suggestion, I disconnected, re-connected, and ran the transaction again.  Unfortunately, nothing changed.

    I then ran your revised version of the transaction using the Error block, and it worked with my purposely error-prone update statement.  The transaction appears to have been properly rolled back (thanks). 

    However - a new problem.  After removing the error from the first update statement, both updates ran properly, but a new error message was generated by the ROLLBACK TRAN request in error block:

      "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

    While the transaction does work as it should, I would feel better if I could find the correct syntax to run a transaction without getting such an error message under any scenario?

    I'd appreciate any further suggestions.

    Thanks again.

     

     

  • Sorry, you need a return statement after the commit.

    BEGIN TRAN

    UPDATE JOBS_Openings

    SET [ID] = SUBSTRING(AARCount, CHARINDEX('(', AARCount) + 1, (CHARINDEX(')', AARCount) - CHARINDEX('(', AARCount)) - 10)

    IF @@ERROR <> 0

      goto Error

    UPDATE JOBS_Openings

    SET PrimarySubjectCode = SUBSTRING(AARPrimary, CHARINDEX('(', AARPrimary) + 1, 2)

    WHERE CHARINDEX('(', AARPrimary) > 0

    IF @@ERROR <> 0

      goto Error

    COMMIT TRAN

    Return

    Error:

     begin

        rollback tran

        return

     end

  • Works great - thank you.

    If, by any chance, you could explain why my original code didn't work, I'd really appreciate it.

    Thanks again for your time.

  • Chris,

    Rollback does not ends the batch or workflow, it simply ... rollsback (@@Trancount =0) all previously started transactions

    I the case where the return was missing you get the error because after the Commit, which does not ends the batch either, you are executing a rollback when there were no pending transactions because commit closed the pending one.

    hth

     


    * Noel

  • Good to know.  Thank you.

  • Here's an article worth reading Implementing Error Handling with Stored Procedures

    Don't be scared by the amount of text, it's crucial that every Transact-SQL coder understands transaction handling fully.

    Also, BOL has some great texts on the subject of transactions and transaction handling.

    /Kenneth

  • Thanks Kenneth.  I'll read the article, and see what I can find in Books Online.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply