Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-25 : 07:21:41
|
| Keith writes "I am trying to find out how to wrap several steps of a SQL 2000 Job (also tried it on 2005) into a transaction.For example my SQL Job has the following steps: steps 1 through 4 - do some processingstep 5 - Begin Transaction TransName steps 6 through 10 - run some other stored procedures that add/update or delete some information in the database. If any of these steps fail the job will skip to step 12 other wise they will proceed to step 11.step 11 - Commit Transaction TransNamestep 12 - Rollback Transaction TransNameThis does not work, steps 11 or 12 will give an error message "Msg 3902, Level 16, State 1, Line 2The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."My thoughts are that this is because the transaction is started in one step (which is a separate session that is ended at the end of the step) and when it gets to the other step (which is another session) it does not see that a transaction exists.I have also tried testing this by having two SQL query windows open and on one of the windows, I start the transaction and on the second window, I try to rollback the transaction but it gives the error mentioned above.Do you know of a way to make the transaction in a SQL Job work?Thanks." |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-25 : 08:46:23
|
| Do your transaction in one job step.CODO ERGO SUM |
 |
|
|
keithlunevich
Starting Member
3 Posts |
Posted - 2006-10-03 : 14:18:14
|
| I can not combine all of the steps into one for the transaction, as each of these steps need to be started, do their processing and then finish before the next step can start. This is due to table constraints. If all of the steps are combined into one then this sequential order will not happen.I have been told to move it into a DTS package and I might try this but I would definitely prefer not to have to rewrite everything.Does anyone else know of another way to use a single transaction over multiple job steps?Thanks,Keith |
|
|
|
|
|
|
Transaction over multiple Job Steps