Transactions and Jobs

[bgreenhouse]

Hi Everyone

I've created a job that consists of 15 steps. Step 1 is simply BEGIN TRAN. Step 14 is ROLLBACK TRAN, and step 15 is COMMIT TRAN.

Every step goes to the next step on success, and on failure goes to step 14. Step 13 goes to step 15 on success.

I get an error when I run step 14 (by forcing an error) or 15 (if everything else runs smoothly). It says it can't find a matching begin transaction.

I realize that perhaps you can't run a transaction like this in a job. My question is: how would I run a transaction in this case? Is a job in it's entirety a transaction (i.e. if one step fails, it rollsback all changes)?

Thanks for any help.

Ben

 

[foxdev]

Hi, Ben:

Is the "use transactions" box checked in the package properties?

See the BOL topic DTS Transaction Fundamentals for a fairly lengthy discussion of DTS packages and transactions, plus links to other related topics. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[bgreenhouse]

Sorry Rob, I should have been clearer:

This isn't a DTS package, this is a Job in the Server Agent. I presume that's what you're thinking...if what you suggest applies to the jobs, could you tell me more?

Thanks

Ben

 

[aalmeida]

You know it is strange but you actually have to use
BEGIN TRANSACTION and COMMIT TRANSACTION the short Tran wont work. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[foxdev]

Errgg; my mistake, Ben; I read too much into it.

If Al's solution does the trick, great. Otherwise, thinking theoretically, I can't see how you could wrap a set of steps in an Agent job in a transaction (reliably, any way), since individual steps within the job may involve actions not normally transcationable (warning: this word may not really exist). For example, one of the steps may involve copying a disk file from one directory to another. Naturally, this action cannot be a part of a SQL Server transaction, because it is an OS-level action, not a database action.

But maybe there is a way that other database actions in other steps can be part of one overall transaction; but I'm not smart enough to know it. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[bgreenhouse]

I guess the key question is whether job steps are atomic. Could you declare variables in step 1 and then use them in step 3 (for instance). If you can't, then I agree with Robert that you shouldn't be able to use BEGIN TRANSACTION and END TRANSACTION in steps.

Al - have you used BEGIN TRANSACTION And COMMIT TRNASACTION in a jobe before (the reason I ask is that I need to have a pretty good idea that this works before I test it - my development environment won't run the job (long story) and I have to trek out to the development server at the production site to test it out, so I want to be sure).

I'm thinking I may put all the steps into one stored procedure...

Thanks

B

 

[aalmeida]

I have used transaction on jobs before but not spaning several steps, I know for sure that if you can put all your steps Begin transaction and Commit transaction into stored procedures it can span several levels of stored procedures.
I do recomend the use of a stored procedure in place of related job steps because a solution based on stored procedures would be more robust and you still can make it a one step job to call the stored procedure. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[bgreenhouse]

Thanks Al

What's the advantage there? You say it's "more robust"...could you elaborate (I believe you, but would like to know ))?

Thanks

Ben

 

[aalmeida]

It means that you can implement more controls on how it runs as well as you can make a better use of global and loca variables, tables and control the use of server and database resources, you can expand on it and change as your requirements change you can compile and debug it, you can change the server or database without changing one line of code, it is just that you add all the advantages of the sprocs to all the good things the jobs have. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"