Situatie
Mai jos este o procedura ce listeaza istoricul joburilor de pe un server MSSQL.
Ea primeste doi parametri
@zile – prin care se alege cu cate zile in urma sa se verifice istoricul
@status – prin care se alege ce fel de istoric se cauta:
0 = ‘Failed’
1 = ‘Success’
2 = ‘Retried’
3 = ‘Cancelled’
Solutie
— exec CheckJobs_prc 10, 1
— 0 = ‘Failed’
— 1 = ‘Success’
— 2 = ‘Retried’
— 3 = ‘Cancelled’
alter procedure CheckJobs_prc
@zile int,
@status int
as
SELECT
sj.name
,sjs.step_id
,sjs.step_name
,CASE sjh.run_status
WHEN 0 THEN ‘Failed’
WHEN 1 THEN ‘Success’
WHEN 2 THEN ‘Retried’
WHEN 3 THEN ‘Cancelled’
END RunStatus
,dbo.agent_datetime(sjh.run_date, sjh.run_time) StepStartTime
,CONVERT(VARCHAR, dbo.agent_datetime(‘19000101’, sjh.run_duration), 8) Duration
,sjh.message
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysjobhistory sjh ON sjs.job_id = sjh.job_id AND sjs.step_id = sjh.step_id
WHERE
sjh.run_status = @status
AND
dbo.agent_datetime(sjh.run_date, sjh.run_time) > DATEADD(DAY, -@zile, SYSDATETIME());
Leave A Comment?