ÿþ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