USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_get_sqlagent_job_status_lastrun]    Script Date: 03/11/2012 08:49:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
-- =============================================
-- Author:        Katherine Villyard
-- Create date: 3/5/2012
-- Description:   Gets status for the last time a job ran.
-- =============================================
CREATE PROCEDURE [dbo].[sp_get_sqlagent_job_status_lastrun]
      -- Add the parameters for the stored procedure here
            @jobname varchar(255)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
declare @run_status int,
            @run_date int,
            @cur_date int,
            @days_since int,
            @rdate datetime,
            @cdate datetime,
            @status varchar(50)
           
set         @cur_date = convert(varchar, getdate(), 112)
           
    -- Insert statements for procedure here
   
(SELECT     @run_status = run_status,
                  @run_date = run_date,
                  @status =
                  CASE
                        WHEN run_status = 1 THEN 'succeeded'
                        WHEN run_status = 0 THEN 'failed'
                        WHEN run_status = 2 THEN 'retry'
                        WHEN run_status = 3 THEN 'canceled'
                  END
FROM         msdb.dbo.sysjobs INNER JOIN
                      msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
WHERE     (msdb.dbo.sysjobs.name = @jobname) AND (msdb.dbo.sysjobhistory.run_date =
                          (SELECT     MAX(sysjobhistory_3.run_date) AS Expr1
FROM         msdb.dbo.sysjobhistory AS sysjobhistory_3 INNER JOIN
                      msdb.dbo.sysjobs ON sysjobhistory_3.job_id = msdb.dbo.sysjobs.job_id
WHERE     (msdb.dbo.sysjobs.name = @jobname))) AND (msdb.dbo.sysjobhistory.run_time =
(SELECT     MAX(sysjobhistory_2.run_time) AS Expr1
FROM         msdb.dbo.sysjobhistory AS sysjobhistory_2 INNER JOIN
                      msdb.dbo.sysjobs ON sysjobhistory_2.job_id = msdb.dbo.sysjobs.job_id
WHERE     (msdb.dbo.sysjobs.name = @jobname) AND step_name='(Job outcome)' AND
(sysjobhistory_2.run_date =
(SELECT     MAX(sysjobhistory_1.run_date) AS Expr1
FROM         msdb.dbo.sysjobhistory AS sysjobhistory_1 INNER JOIN
                      msdb.dbo.sysjobs ON sysjobhistory_1.job_id = msdb.dbo.sysjobs.job_id
WHERE     (msdb.dbo.sysjobs.name = @jobname) and step_name='(Job outcome)'))))
            and step_name='(Job outcome)')
           
set @rdate = CAST(convert(varchar,@run_date) as datetime)
set @cdate = CAST(convert(varchar,@cur_date) as datetime)
set @days_since = convert(int,(@cdate - @rdate))
 
print 'The job ' + @status + ' when it was last run ' + convert(varchar,@days_since) + ' day(s) ago on ' + convert(varchar,@rdate,101) + '.'
select @run_status as status_code, @days_since as days_since, @status as status
 
END
GO


