--how to run only a step of a job
basically I save the current situation of the step within a job - particularly what to do when success and what to do if fails.
then I change it to end the job in success or failure accordingly.
here is the script:
USE [msdb]
GO
declare @on_success_action int,
@on_fail_action int,
@run_the_job bit = 0
if object_id('tempdb..#the_only_step',N'U') is not null
drop table #the_only_step
create table #the_only_step(
step_id int,-- Unique identifier for the step.
step_name sysname,-- Name of the step in the job.
subsystem nvarchar(40),-- Subsystem in which to execute the step command.
command nvarchar(max),-- Command executed in the step.
flags int,-- A bitmask of values that control step behavior.
cmdexec_success_code int,-- For a CmdExec step, this is the process exit code of a successful command.
on_success_action tinyint,-- Action to take if the step succeeds:
--1 = Quit the job reporting success.
--2 = Quit the job reporting failure.
--3 = Go to the next step.
--4 = Go to step.
on_success_step_id int,-- If on_success_action is 4, this indicates the next step to execute.
on_fail_action tinyint,-- What to do if the step fails. Values are same as on_success_action.
on_fail_step_id int,-- If on_fail_action is 4, this indicates the next step to execute.
server varchar(100) null,-- Reserved.
database_name varchar(100) null,-- For a Transact-SQL step, this is the database in which the command executes.
database_user_name varchar(100) null,-- For a Transact-SQL step, this is the database user context in which the command executes.
retry_attempts int,-- Maximum number of times the command should be retried (if it is unsuccessful).
retry_interval int,-- Interval (in minutes) for any retry attempts.
os_run_priority int,-- Reserved.
output_file_name nvarchar(200),-- File to which command output should be written (Transact-SQL, CmdExec, and PowerShell steps only).
last_run_outcome int,-- Outcome of the step the last time it ran:
--0 = Failed
--1 = Succeeded
--2 = Retry
--3 = Canceled
--5 = Unknown
last_run_duration int,-- Duration (hhmmss) of the step the last time it ran.
last_run_retries int,-- Number of times the command was retried the last time the step ran.
last_run_date int,-- Date the step last started execution.
last_run_time int,-- Time the step last started execution.
proxy_id int
)
insert into #the_only_step
EXEC msdb.dbo.sp_help_jobstep
@job_name=N'SUPT_munichRE - after restore',
@step_id=2
--you can check if things are there as expected
select * from #the_only_step
--we want to change the step:
--after finished - success and after finished - failure - either way end the job
select @on_success_action= a.on_success_action,
@on_fail_action = a.on_fail_action
from #the_only_step a
EXEC msdb.dbo.sp_update_jobstep
@job_name=N'SUPT_munichRE - after restore',
@step_id=2 ,
@on_success_action=1,
@on_fail_action=2
if (@run_the_job = 1)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name=N'SUPT_munichRE - after restore',
@step_name = N'2'
PRINT 'job started'
END
--after it finishes running
--set the values back as they originally were
EXEC msdb.dbo.sp_update_jobstep
@job_name=N'SUPT_munichRE - after restore',
@step_id=2 ,
@on_success_action=@on_success_action,
@on_fail_action=@on_fail_action
Now another interesting point is
after starting the job, how to wait until it is finished before moving on?
that is answered here:
--[is there a way to run the same job multiple times without scheduling it?][1]
--An INSERT EXEC statement cannot be nested.
--https://mcmap.net/q/172710/-errors-quot-insert-exec-statement-cannot-be-nested-quot-and-quot-cannot-use-the-rollback-statement-within-an-insert-exec-statement-quot-how-to-solve-this
Basically I use that stored procedure to run the job instead of msdb.dbo.sp_start_job as in the script above.
hope this helps
as it is very useful to me!
