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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy