ZOUHAIER KHARROUBI

-- =============================================

-- Author:     KHARROUBI Zouhaier

-- Create date: 12/02/2021

-- Description: Modification de la planification d'un job SQL Server

-- =============================================

DECLARE @schedule_id AS INT

--Afficher la planification avant la modification

SELECT @@SERVERNAME InstanceSQL, T1.name,T4.*

FROM msdb.dbo.sysjobs T1

JOIN msdb.dbo.sysjobschedules AS T2 ON T1.job_id = T2.job_id

JOIN msdb.dbo.sysjobservers AS T3 ON T3.job_id = T2.job_id

JOIN msdb.dbo.sysschedules T4 ON T4.schedule_id=T2.schedule_id

WHERE T3.server_id = 0

AND T1.name='MonJob_1'

--Récupérer l'identifiant de la planification

SELECT @schedule_id=T2.schedule_id

FROM msdb.dbo.sysjobs T1

JOIN msdb.dbo.sysjobschedules AS T2 ON T1.job_id = T2.job_id

JOIN msdb.dbo.sysjobservers AS T3 ON T3.job_id = T2.job_id

WHERE T3.server_id = 0

AND T1.name='MonJob_1'

--Appliquer la modification souhaitée

EXEC msdb.dbo.sp_update_schedule @schedule_id=@schedule_id

,@active_start_time=200500

,@freq_interval=32 --Vendredi=32 , Samedi=64

--Afficher la planification après la modification

SELECT @@SERVERNAME InstanceSQL, T1.name,T4.*

FROM msdb.dbo.sysjobs T1

JOIN msdb.dbo.sysjobschedules AS T2 ON T1.job_id = T2.job_id

JOIN msdb.dbo.sysjobservers AS T3 ON T3.job_id = T2.job_id

JOIN msdb.dbo.sysschedules T4 ON T4.schedule_id=T2.schedule_id

WHERE T3.server_id = 0

AND T1.name='MonJob_1'