Allgemein

Ich brauchte eine Übersicht von SQL Agent Jobs (Name, LastRunDate, Current Execution Status und LastRunOutcome), doch da die Job Hisorty auf 10 Tage auf diesem Server begrenzt ist, fällt der Ansatz über die Tabellen msdb.dbo.sysjobs und msdb.dbo.sysjobhistory aus (siehe Beispiel).

select distinct [name] as 'Job Name',
	case [enabled] when 1 then 'Enabled' else 'Disabled' end as 'Enabled',
	cast (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') as datetime) as 'Last Run',
	step_id as Step,
	case [h].[run_status]
		when 0 then 'Failed' else 'Success'
		end as 'Status'
from msdb.dbo.sysjobs         j
left join msdb.dbo.sysjobschedules s
	on j.job_id = s.job_id
LEFT join msdb.dbo.sysjobhistory h
	on j.job_id = h.job_id
where step_id = 0
     and h.instance_id in (select max(sh.instance_id)
				from msdb.dbo.sysjobs sj
				join msdb.dbo.sysjobhistory sh
					on sj.job_id = sh.job_id
				where h.step_id = 0
				group by sj.[name])

Die einzigste Möglichkeit von allen Jobs diese Werte zu ermitteln, ist über die Prozedur sp_help_job. Leider ist es nicht möglich das Ergebnis in eine TempTable umzuleiten (siehe Beispiel)

insert into #TempJobTable
   execute sp_help_job

Dies Endet dann meist in folgender Fehlermeldung:

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Nun bleibt nur noch der Weg über OPENROWSET (siehe Beispiel) was wunderbar funktioniert.

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'set fmtonly off exec msdb.dbo.sp_help_job')
Select
[name],
cast(ltrim(str(last_run_date))+' '+stuff(stuff(right('000000'+ltrim(str(last_run_time)), 6) , 3, 0, ':'), 6, 0, ':') as NVARCHAR(255)) as 'Last Run',
CASE [current_execution_status]
	WHEN 1 THEN 'Executing'
	WHEN 2 THEN 'Waiting For Thread'
	WHEN 3 THEN 'Between Retries'
	WHEN 4 THEN 'Idle'
	WHEN 5 THEN 'Suspended'
	WHEN 7 THEN 'PerformingCompletion'
	ELSE 'Unknown'
	END AS Status
, CASE [last_run_outcome]
	WHEN 0 THEN 'Failed'
	WHEN 1 THEN 'Success'
	ELSE 'Unknown'
	END AS Result
from #JobInfo
ORDER BY [name]
DROP TABLE #JobInfo