USE msdb;
GO

BEGIN TRANSACTION;

DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;

IF NOT EXISTS (SELECT name 
               FROM   msdb.dbo.syscategories 
               WHERE  name=N'Surveillance' 
                      AND category_class=1)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category 
        @class=N'JOB', 
        @type=N'LOCAL', 
        @name=N'Surveillance'
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
      GOTO QuitWithRollback;
END;

DECLARE @jobId BINARY(16);
EXEC @ReturnCode =  msdb.dbo.sp_add_job 
     @job_name=N'! SQLpro - Surveillance blocages arborescent', 
	  @enabled=1, 
	  @notify_level_eventlog=0, 
	  @notify_level_email=0, 
	  @notify_level_netsend=0, 
	  @notify_level_page=0, 
	  @delete_level=0, 
	  @description=N'Pas de description disponible.', 
	  @category_name=N'Surveillance', 
	  @owner_login_name=N'sa', @job_id = @jobId OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
     @job_id=@jobId, @step_name=N'Analyse et trace des blocages', 
	  @step_id=1, 
	  @cmdexec_success_code=0, 
	  @on_success_action=1, 
	  @on_success_step_id=0, 
	  @on_fail_action=2, 
	  @on_fail_step_id=0, 
	  @retry_attempts=0, 
	  @retry_interval=0, 
	  @os_run_priority=0, @subsystem=N'TSQL', 
	  @command=N'IF NOT EXISTS
(
-- runs only if the same SQL Agent job is not running
SELECT *
FROM   msdb.dbo.sysjobs_view AS JV
       LEFT OUTER JOIN msdb.dbo.sysjobactivity AS JA 
          ON JV.job_id = JA.job_id
       LEFT OUTER JOIN msdb.dbo.syssessions AS S
          ON S.session_id = JA.session_id
       JOIN (SELECT MAX(agent_start_date) AS LAST_START_DATE        
             FROM   msdb.dbo.syssessions) AS SM
          ON S.agent_start_date = SM.LAST_START_DATE
WHERE  run_requested_date IS NOT NULL AND stop_execution_date IS NULL
       AND name = N''! SQLpro - Surveillance blocages arborescent''
) EXEC msdb.S_SURVEY.P_SQLPRO_TREEBLOKERS
   @DURATION_S_ALERT = 30, 
   @BLOCKED_COUNT_ALERT = 5,
   @BLOCKED_DEEP_ALERT = 3,
   @DELETE_DEEP_DAYS = 90;', 
		@database_name=N'master', 
		@flags=20;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_update_job 
     @job_id = @jobId, 
     @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule 
     @job_id=@jobId, @name=N'Planif surveillance blocages SQLpro', 
	  @enabled=1, 
	  @freq_type=4, 
	  @freq_interval=1, 
	  @freq_subday_type=2, 
	  @freq_subday_interval=15, 
	  @freq_relative_interval=0, 
	  @freq_recurrence_factor=0, 
	  @active_start_date=20240520, 
	  @active_end_date=99991231, 
	  @active_start_time=0, 
	  @active_end_time=235959, 
	  @schedule_uid=N'b941328c-920a-4224-993b-cecf4257d0b9';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver 
     @job_id = @jobId, 
     @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
   GOTO QuitWithRollback;

COMMIT TRANSACTION;

GOTO EndSave;

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:

GO