I have always felt that the traditional approach to getting Agent Job status by creating a table and populating it with output from xp_sqlagent_enum_jobs was incredibly crude. So I poked around a bit and came up with a solution using a Table Valued Function that queries sysjobactivity to determine the status of a given job. The reason I use a Table Valued Function is because we have a few tables that contain a list of jobs that need to be started at certain points in a given process (and they may or may not already be running) - with the TVF, you can simply CROSS APPLY the function and bob's your uncle.
I ran in to this the other day, did some googling, and really did not like what I saw for workarounds. In SQL Server 2005, when an XMLA job step fails (returns an Exception node in the XML response), the job step still reports success (because it's defining success as "did I get a response") - this has been fixed in SQL Server 2008. Common workarounds are using ascmd.exe or SSIS to handle the XMLA commands (ish - both of those solutions add a lot of complexity for a simple problem). So, I came up with a workaround that checks the text of the previous job step for the substring "<Exception ". It's been working thus far, with no issues.
After each XMLA command step, insert a T-SQL step to verify that the XMLA command step succeeded:
DECLARE @JobName VARCHAR(64) SET @JobName = ‘Name Of Job This Step Belongs to’ DECLARE @Message VARCHAR(1024) SELECT TOP 1 @Message = CAST([message] AS VARCHAR(1024)) FROM msdb.dbo.sysjobhistory a INNER JOIN msdb.dbo.sysjobs b ON a.job_id = b.job_id AND b.[NAME] = @JobName ORDER BY run_date DESC, run_time DESC, step_id DESC IF @Message LIKE ‘%<Exception %’ RAISERROR (@Message, 17, 1)
UPDATE (2009-04-03): Added
, step_id DESC to
ORDER BY clause - when the XMLA job fails instantly (say you tried to process a nonexistant partition), run_time doesn't have enough granularity to sort properly.
Once your done, your job steps will look something like this: