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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating