December 5, 2005 at 5:04 pm
We have a job that our software installs as part of it's package. We then often make customizations to the job for our various customers. We need to now add 2 steps in the middle of the job to all of our customers. So if the job has steps 1 through 10, we need to add 2 new steps between steps 4 and 5, resulting in 12 steps. Some customers may have 12 steps in their current job, but still need these 2 new steps.
I tried using EM to generate a change script that I could tweak, and also tried Profiling what EM was doing, but neither gave me anything I could really use.
I tried looking on the web, but couldn't find anything either.
Thanks for your help,
Rick Todd
December 6, 2005 at 7:07 am
I am certainly no Agent or job expert, but you might want to check out modifying the job using SQL-DMO and the job-related objects. You should be able to create a new Job object and add it to the Jobs collection. I've dabbled with SQL-DMO's other objects and found them pretty easy to work with. Just a quick idea...
December 6, 2005 at 8:45 am
use
msdb
-- Find the Job ID and the step numbers involved
declare @new_step int
declare @jobid uniqueidentifier
select @jobid = sj.job_id, @new_step = max(sjs.step_id) + 1
from sysjobs sj
inner join sysjobsteps sjs on sj.job_id = sjs.job_id
where sj.name = '<job name>'
declare @previous_step int, @next_step int, @last_step int
-- If you're sure the step numbers are fixed on all customer systems, you could use constants and skip the lookups
-- Find the step id of the previous step, and of the following (on success) step
select @previous_step = step_id,
@next_step = case when on_success_action = 3 then step_id + 1 else on_success_step_id end
from sysjobsteps
where job_id = @jobid and step_name = '<prev step>'
-- Find the step id of the last step
select @last_step = step_id
from sysjobsteps where job_id = @jobid and step_name = '<last step>'
-- Add first new step, on success go to next new step
exec sp_add_jobstep @job_id = @jobid
, @step_id = @new_step
{ , [ @step_name = ] 'step_name' }
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @command = ] 'command' ]
[ , [ @additional_parameters = ] 'parameters' ]
[ , [ @cmdexec_success_code = ] code ]
, @on_success_action = 3
[ , [ @on_success_step_id = ] success_step_id ]
[ , [ @on_fail_action = ] fail_action ]
[ , [ @on_fail_step_id = ] fail_step_id ]
[ , [ @server = ] 'server' ]
[ , [ @database_name = ] 'database' ]
[ , [ @database_user_name = ] 'user' ]
[ , [ @retry_attempts = ] retry_attempts ]
[ , [ @retry_interval = ] retry_interval ]
[ , [ @os_run_priority = ] run_priority ]
[ , [ @output_file_name = ] 'file_name' ]
[ , [ @flags = ] flags ]
-- Add second new step, on success go to previous on-success step
exec sp_add_jobstep @job_id = @jobid
, @step_id = @new_step + 1
{ , [ @step_name = ] 'step_name' }
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @command = ] 'command' ]
[ , [ @additional_parameters = ] 'parameters' ]
[ , [ @cmdexec_success_code = ] code ]
, @on_success_action = 4
, @on_success_step_id = @next_step
[ , [ @on_fail_action = ] fail_action ]
[ , [ @on_fail_step_id = ] fail_step_id ]
[ , [ @server = ] 'server' ]
[ , [ @database_name = ] 'database' ]
[ , [ @database_user_name = ] 'user' ]
[ , [ @retry_attempts = ] retry_attempts ]
[ , [ @retry_interval = ] retry_interval ]
[ , [ @os_run_priority = ] run_priority ]
[ , [ @output_file_name = ] 'file_name' ]
[ , [ @flags = ] flags ]
-- Update the previous step to jump to the new step on success
exec sp_update_jobstep @job_id = @jobid,
@step_id = @previous_step
, @on_success_action = 4
, @on_success_step_id = @new_step
[, [@on_fail_action =] fail_action]
[, [@on_fail_step_id =] fail_step_id]
-- Update the last step to quit the job, so it doesn't re-execute the new steps
exec sp_update_jobstep @job_id = @jobid,
@step_id = @last_step
, @on_success_action = 1
, @on_fail_action = 2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply