USE [Control]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_DetermineJobRunStatus]    Script Date: 02/21/2006 17:00:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_DetermineJobRunStatus] (@JobName AS SYSNAME) 
RETURNS INT
AS
/*
PROJECT:	BAU
AUTHOR:		FRANK BAZAN
DATE:		22/11/2005
DESCRIPTION:	FUNCTION TO DETERMINE THE CURRENT RUN STATUS OF
		ANY SQL AGENT JOB ON THE SERVER

RETURNS:	0 = FAILURE
		1 = SUCCESS
		3 = CANCELLED
==================================================================
History		Author		Date		Description
==================================================================
1.0		Frank Bazan	22/11/2005	First Version
1.1		Frank Bazan	09/12/2005	Change to source table.
						Using sysjobservers, instead of
						sysjobhistory gives	a more
						reliable last run status

*/



BEGIN
DECLARE	@SystemJobID 	UNIQUEIDENTIFIER
DECLARE @RunStatus		INT

SET 	@SystemJobID = (SELECT	job_id
			FROM	msdb.dbo.sysjobs
			WHERE	name = @JobName)

SET		@RunStatus = 
		(	SELECT	last_run_outcome
			FROM	msdb.dbo.sysjobservers
			WHERE	job_id = @SystemJobID )

RETURN 	@RunStatus
END