Post reply

Transaction in SQL Job Steps

  • September 22, 2006 at 8:40 am

    #153197

    Hello,

    I am trying to add a transaction to a SQL Job. My Job has 12 steps.

    Steps 1-4: Do some processing

    Step 5: Start Transaction

    Step 6-8: Do some updating - If any of these steps fail they jump to step 12

    Step 9: Commit Transaction

    Step 10-11: Do some processing - Job stops when these steps are finished

    Step 12: Rollback Transaction

    The problem is that either the Commit Transaction or the Rollback Transaction error with the messages of "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." or "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". I think the reason for this is that each Job step runs in its own session which are independent of each other.

    Does anybody know of a way to get this working?

    Thanks

  • September 22, 2006 at 9:37 am

    #662011

    DTS can handle transactions so Can you add the jobs steps in DTS and schedule the DTS?

    Thanks

    Sreejith

  • September 22, 2006 at 12:25 pm

    #662024

    Hi Sreejith,

    The reason why we are using the SQL Jobs is that it runs the steps in sequential order (that is every step must start, do its processing and then finish before the next step can start). If this does not happen then due to data constraints, an error could possibly occur.

    I believe that with a DTS package there is not this ability so it is not an option.

    Thanks,

    Keith

  • September 22, 2006 at 2:36 pm

    #662041

    You can add multiple "Execute SQL Tasks" and define works flows. Check it out.

    Thanks

    Sreejith

Viewing 4 posts - 1 through 3 (of 3 total)

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