Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I update the last jobstep of a job?

Status
Not open for further replies.

ratinakage

Programmer
Feb 26, 2006
18
ZA
Hi all.

I am trying to update the last job step in a job, before adding a final job step to the end of the job. In the below example, the last job step number is 5. but this is not always the case. Is there a way to ask SQL to update the last job step??

Thanks.

exec msdb..sp_update_jobstep @job_name = N'The Office - Batch Process - Data Summary Base Tables',
@step_id = 5,
@on_success_action = 3 ,
@on_fail_action = 3
GO
 
I usually go into Enterprise Manager and right click onthe job and choos properties. Then go the the step tab, choose the step I want to change and selct Edit.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Yeh.

Thats how I would do it too. But I need to write it in an SQL script so it can be rolled out and installed in the background at lots of different places.

If someone has a different number of job steps, theres gonna be sql errors and the install will fail. :(

anyone else got any idea??

thanks
 
You can use sp_update_jobstep

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
To find out how the job looks, use sp_help_job and/or sp_help_jobstep

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Oh, I see you already know sp_update_jobstep. Didn't read the whole thing! :p

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
hmmm.

I think they use a loop to increment jobstep id which kinda gives me an idea.

I could maybe somehow loop through the job steps until I found the one which has attribute: on_success_action == 1 (quit with success). Then change it to on_success_action = 3 and then add my job step to the end.

Im kinda new to sql. can i write loops? can i ask sql what the on_success_action of a jobstep is?

anyone know how I would go about coding this...?

thanks!
 
Yes, you can do loops. "For each xxx in yyy etc. don't forget to add one to your step though.
Looks like the MS article reads the job steps into memory, alters them and then wrtites the job back.
Have a play in a development environment is the best I can say.
 
think im getting closer to a solution.

i never realised (or never thought about the fact that) sp_update_jobstep is a normal stored procedure that i can look up in enteprise manager under msdb. now i can see which table it accesses msdb.sysjobs and msdb.sysjobsteps.

now all i need to do is count the number of jobsteps for a particular job. and thats the number of my last jobstep! :)

this is probably kids stuff for you guys. i'll post the sql when im done...
 
the answer:

how to update the last step of a job
-------------------------------------

declare @numsteps INT

SET @numsteps = (SELECT count (step_id) from msdb.dbo.sysjobsteps
where job_id = (SELECT job_id from msdb.dbo.sysjobs
where name = 'Postilion Office - Batch Process - Data Summary Base Tables'))

exec msdb..sp_update_jobstep @job_name = N'The Office - Cleaner - Data Summary Base Tables',
@step_id = @numsteps,
@on_success_action = 1 ,
@on_fail_action = 1
GO
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top