June 10, 2013 at 8:02 pm
in the highlighted value else 0 instead i want 'not enabled' and 'not scheduled'
SELECT
distinct @@SERVERNAME AS ServerName,
CASE
WHEN J.Name IS NOT NULL THEN 1 -- job exists
ELSE 0 -- Job does not exist
END AS IsExisting,
CASE J.enabled
WHEN 1 Then 1 -- job is enabled
ELSE 0 -- instead of 0 i want to return 'not enabled' job does not exist error condition
END AS IsEnabled,
CASE
WHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabled
ELSE 0--instead of 0 i want to return 'not scheduled'
END AS IsScheduleEnabled ,
J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date)
END AS LASTRUNDATE
,getdate() as Date
FROM MSDB..sysjobs J
LEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_id
LEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_id
LEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_id
LEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_id
AND steps.step_id = 1
where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null)
GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date
June 10, 2013 at 8:13 pm
This:
SELECT
distinct @@SERVERNAME AS ServerName,
CASE
WHEN J.Name IS NOT NULL THEN cast(1 as varchar) -- job exists
ELSE 'does not exist' -- 0 Job does not exist
END AS IsExisting,
CASE J.enabled
WHEN 1 Then cast(1 as varchar) -- job is enabled
ELSE 'not enabled' --0 instead of 0 i want to return 'not enabled' job does not exist error condition
END AS IsEnabled,
CASE
WHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabled
ELSE 0--instead of 0 i want to return 'not scheduled'
END AS IsScheduleEnabled ,
J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date)
END AS LASTRUNDATE
,getdate() as Date
FROM MSDB..sysjobs J
LEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_id
LEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_id
LEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_id
LEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_id
AND steps.step_id = 1
where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null)
GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply