CREATE PROCEDURE usp_job_history
@dateparam DATETIME
AS
SELECT dbo.sysjobhistory.server, dbo.sysjobs.name AS job_name,
CASE dbo.sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END as run_status, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobhistory.step_id, dbo.sysjobhistory.step_name, dbo.sysjobhistory.run_duration, dbo.sysjobhistory.message
FROM dbo.sysjobhistory INNER JOIN
dbo.sysjobs ON dbo.sysjobhistory.job_id = dbo.sysjobs.job_id
WHERE dbo.sysjobs.category_id = 0 and dbo.sysjobhistory.run_date = datepart(yyyy,@dateparam)*10000 + datepart(mm,@dateparam)*100 + datepart(dd,@dateparam)
ORDER BY dbo.sysjobhistory.server, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobs.name, dbo.sysjobhistory.step_id
GO
--Example: EXEC usp_job_history '10/02/2004'
Check out
http://qa.sqlservercentral.com/scripts/Miscellaneous/31277/[/url]
http://qa.sqlservercentral.com/scripts/Miscellaneous/30159/[/url]