Technical Article

Add empty first step for every job

,

This script adds empty first step for every job for easier job history viewing.
By default, you can see information about job launch only after completion of first step.
/*

This script adds empty first step for every job for easier job history viewing.
By default, you can see information about job launch only after completion of first step.

*/
USE msdb;
SET NOCOUNT ON;

DECLARE @tmp_sp_help_jobstep TABLE 
    (
      step_id INT NULL ,
      step_name NVARCHAR(128) NULL ,
      subsystem NVARCHAR(128) COLLATE Latin1_General_CI_AS NULL ,
      command NVARCHAR(MAX) NULL ,
      flags INT NULL ,
      cmdexec_success_code INT NULL ,
      on_success_action TINYINT NULL ,
      on_success_step_id INT NULL ,
      on_fail_action TINYINT NULL ,
      on_fail_step_id INT NULL ,
      server NVARCHAR(128) NULL ,
      database_name SYSNAME NULL ,
      database_user_name SYSNAME NULL ,
      retry_attempts INT NULL ,
      retry_interval INT NULL ,
      os_run_priority INT NULL ,
      output_file_name NVARCHAR(300) NULL ,
      last_run_outcome INT NULL ,
      last_run_duration INT NULL ,
      last_run_retries INT NULL ,
      last_run_date INT NULL ,
      last_run_time INT NULL ,
      proxy_id INT NULL ,
      job_id UNIQUEIDENTIFIER NULL
)

DECLARE @cur_job_name VARCHAR(1000)

DECLARE job_cursor CURSOR
FOR
SELECT [name]  FROM msdb..sysjobs;

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @cur_job_name;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
print @cur_job_name;

delete from @tmp_sp_help_jobstep;

BEGIN TRY
INSERT INTO @tmp_sp_help_jobstep (
step_id ,
step_name ,
subsystem ,
command ,
flags ,
cmdexec_success_code ,
on_success_action ,
on_success_step_id ,
on_fail_action ,
on_fail_step_id ,
server ,
database_name ,
database_user_name ,
retry_attempts ,
retry_interval ,
os_run_priority ,
output_file_name ,
last_run_outcome ,
last_run_duration ,
last_run_retries ,
last_run_date ,
last_run_time ,
proxy_id
)
EXEC dbo.sp_help_jobstep
@job_name = @cur_job_name,
@step_id = 1 ;
END TRY
BEGIN CATCH
END CATCH;

IF NOT EXISTS(SELECT * from @tmp_sp_help_jobstep WHERE step_name = N'start step')
BEGIN
EXEC sp_add_jobstep
@job_name = @cur_job_name,
@step_name = N'start step',
@subsystem = N'TSQL',
@command = N'-- start step', 
@step_id = 1,
@on_success_action = 3, -- Go to next step
@on_fail_action = 3; -- Go to next step

PRINT '  start step added'
END


FETCH NEXT FROM job_cursor INTO @cur_job_name;

END 
CLOSE job_cursor;
DEALLOCATE job_cursor;

Rate

Share

Share

Rate