[SQL Server] Modification de la planification d'un job SQL Server
-- =============================================
-- 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'