Bonjour,

j'ai une requête pour voir si les job sql agent s'exécutent en respectant leurs planifications, pour cela j'ai listé toutes les dates de planification a partir de la date de première exécution de la planification
J'ai défini deux paramètres date pour spécifier sur quelle plage horaire je souhaite consulter et après comparé ces dates avec les dates de l'historique dans la tables sysjobhistory c'est a dire si il y a une correspondance entre ces dates alors je sais que le job s'est exécuté en respectant sa planification
mais si c'est le contraire alors il doit me renvoyé null pour que je détecte que le job s'est pas exécuté en respectant sa planification.
Le problème la requête me renvoie null dans la colonne DateExecution alors que c'est pas normale.

Je vous remercie pour votre aide.

voici la requête,



DECLARE @DateDebut1 DATETIME
SET @DateDebut1 = '2023-01-01'

DECLARE @DateFin DATETIME
SET @DateFin = GETDATE()

-- Créer une table temporaire pour stocker les résultats
CREATE TABLE #DatesExecutionTemp (
NomJob VARCHAR(255),
NomPlanification VARCHAR(255),
DateExecution DATETIME,
Frequence VARCHAR(100)
)

-- boucle cursor pour parcourir les planifications du job
DECLARE @NomJob VARCHAR(255)
DECLARE @NomPlanification VARCHAR(255)
DECLARE @DateExecution DATETIME
DECLARE @Frequence VARCHAR(100)
DECLARE @jobId UNIQUEIDENTIFIER
DECLARE curPlanifications CURSOR FOR
SELECT
j.name AS NomJob,
s.name AS NomPlanification,
TRY_CONVERT(DATETIME, CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')) AS DateExecution,
CASE
WHEN s.freq_type = 1 THEN 'Une seule fois'
WHEN s.freq_type = 4 THEN 'Quotidienne'
WHEN s.freq_type = 8 THEN 'Hebdomadaire'
WHEN s.freq_type = 16 THEN 'Mensuelle'
WHEN s.freq_type = 32 THEN 'Mensuelle relative'
END AS Frequence
FROM
sysschedules s
JOIN sysjobschedules js ON js.schedule_id = s.schedule_id
LEFT JOIN sysjobs j ON j.job_id = js.job_id
WHERE
TRY_CONVERT(DATETIME, CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')) <= @DateFin
AND s.enabled = 1

ORDER BY j.name ASC, DateExecution DESC

OPEN curPlanifications
FETCH NEXT FROM curPlanifications INTO @NomJob, @NomPlanification, @DateExecution, @Frequence

-- boucle pour générer les dates d'exécution
WHILE @@FETCH_STATUS = 0
BEGIN
-- ajout de la date d'exécution initiale dans la table temporaire
INSERT INTO #DatesExecutionTemp (NomJob, NomPlanification, DateExecution, Frequence)
VALUES (@NomJob, @NomPlanification, @DateExecution, @Frequence)

-- Boucle WHILE pour générer les dates d'exécution suivantes en fonction de la fréquence
WHILE @DateExecution <= @DateFin
BEGIN
-- Calcul de la prochaine date d'exécution en fonction de la fréquence
SET @DateExecution =
CASE
WHEN @Frequence = 'Quotidienne' THEN DATEADD(DAY, 1, @DateExecution) -- Quotidienne : ajout d'un jour
WHEN @Frequence = 'Hebdomadaire' THEN DATEADD(WEEK, 1, @DateExecution) -- Hebdomadaire : ajout d'une semaine
WHEN @Frequence = 'Mensuelle' THEN DATEADD(MONTH, 1, @DateExecution) -- Mensuelle : ajout d'un mois
WHEN @Frequence = 'Mensuelle relative' THEN DATEADD(MONTH, 1, @DateExecution) -- Mensuelle relative : ajout d'un mois
END

-- insertion de la nouvelle date d'exécution dans la table temporaire
INSERT INTO #DatesExecutionTemp (NomJob, NomPlanification, DateExecution, Frequence)
VALUES (@NomJob, @NomPlanification, @DateExecution, @Frequence)
END

FETCH NEXT FROM curPlanifications INTO @NomJob, @NomPlanification, @DateExecution, @Frequence
END

CLOSE curPlanifications
DEALLOCATE curPlanifications

SELECT
dt.NomJob,
dt.NomPlanification,
CASE
WHEN EXISTS (
SELECT 1
FROM sysjobhistory h
JOIN sysjobs j ON h.job_id = j.job_id
JOIN sysjobschedules js ON js.job_id = j.job_id
JOIN sysschedules s ON s.schedule_id = js.schedule_id
WHERE CONVERT(VARCHAR(8), h.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), h.run_time), 6), 3, 0, ':'), 6, 0, ':')
= CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')
AND h.step_id = 0

)
THEN dt.DateExecution
ELSE NULL
END AS DateExecution,
dt.Frequence
FROM #DatesExecutionTemp AS dt
WHERE dt.DateExecution BETWEEN @DateDebut1 AND @DateFin
ORDER BY dt.DateExecution DESC, dt.NomJob ASC

DROP TABLE #DatesExecutionTemp