Script below. This one works better for Sql Server 2000. I will do a version for Sql Server 2005, when I get a chance. It didn’t work very well on Sql Server 2005 because Ad Hoc Distributed Queries is disabled by default.

set nocount on
IF OBJECT_ID(‘tempdb..#TmpJobs’) IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET(‘sqloledb’
, ‘server=(local);trusted_connection=yes’
, ‘set fmtonly off exec msdb.dbo.sp_help_job @enabled = 1’)

select ‘Successful run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(‘0’+CAST((last_run_time/10000) AS VARCHAR), 2)+’:’+
RIGHT(‘0’+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:’+
RIGHT(‘0’+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 1

select ‘Failed run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(‘0’+CAST((last_run_time/10000) AS VARCHAR), 2)+’:’+
RIGHT(‘0’+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:’+
RIGHT(‘0’+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 0