How do I update the last jobstep of a job?

[ratinakage]

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

 

[SQLSister]

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

 

[ratinakage]

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

 

[williamkremer]

You can use sp_update_jobstep

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

 

[williamkremer]

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

 

[williamkremer]

Oh, I see you already know sp_update_jobstep. Didn't read the whole thing!

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

 

[ratinakage]

anyone else got any idea?

 

[SQLWilts]

Does this help?

http://msdn2.microsoft.com/en-us/library/ms135739.aspx

 

[ratinakage]

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!

 

[SQLWilts]

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.

 

[SQLWilts]

This:

http://msdn2.microsoft.com/en-us/library/ms142613.aspx

talks about the BeginAlter and getting jobstep object from jobsteps.

Best of luck, and I would be interested in seeing your code when you have cracked it

 

[ratinakage]

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...

 

[ratinakage]

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