One of my responsibilities each day is to verify that SQL Server SSIS, and other jobs run via the SQL Server Agent, have run successfully. For some time I did that via e-mails from the jobs and Event Log entries on the server. Then when I was researching SQL Sever Agent job processing I found that it keeps a record of the last results of jobs it runs and that there was a system stored procedure, sp_help_job, that provided that information. You can see the documentation on sp_help_job in Books Online at http://msdn.microsoft.com/en-us/library/ms186722.aspx. There are a number of parameters associated with it but for my purposes the only parameter I wanted to provide was @enabled=1 so I get the jobs that are enabled. This would provide me information on the results of jobs, but how should I then display this information?

I decided to create a SQL Server Reporting Services (SSRS) report that would display that information for me. I originally tried just using "sp_help_job @enabled=1" as my data source for the report. However the report designer wasn't able to get a field list from that for some reason. After some experimentation I tried creating a stored procedure called sp_JobStats with "EXEC msdb.dbo.sp_help_job @enabled=1" in it and that worked. I was never able to figure out what the problem was with executing the sp_help_job stored procedure directly.

I choose to put the sp_JobStats stored procedure into one of my user databases (ExactaInternal) instead of the msdb database because I wasn't sure what would happen with the stored procedure when I upgraded from one version of SQL Server to the next. The report uses the ExactaInternal.rds Shared Data Source. You will need to modify this as required for your environment.

Even though I don't normally do so, I used the sa user ID for the data source because other ID's didn't seem to produce output. You can't use integrated security when you are going to create subscriptions to a report. The images below demonstrate how the data source would need to be modified for your environment.

I started with a standard table report using the results of the sp_JobStats stored procedure as input. I grouped the output by Status (Running vs. Not Running) and then Last Run Date. The grouping by Status would quickly tell me if there was a job that was hung and still running. The sorting I used in the report table was by Status, Run Date and then Run Time. The normal output of the procedure included some jobs that I didn't really want to include because either they were SQL Server Report server jobs or replication jobs (the status of replication was checked through other means). Therefore I added a filter list to exclude jobs that had the categories I didn't want to see. You need to right-click on the table in the report and choose Properties to see the above settings.

The first row of the report is a grouping by job status. The value is an IIF expression that provides a translation of the Status of the job to the text values "Running", "Not Running" or "Other". The second row of the report is the grouping by the last run date. It should be noted that the date and time fields from sp_help_job are not of date/time type, but are instead integer fields. So they can't as easily be formatted and I just chose to display them as integers. The detail lines then provide the desired information on the job.

The columns use the appropriate fields from the stored procedure output. For the Last Run Outcome column I used an IIF expression to translate the numeric run outcome values to the text "Succeeded", "Failed" or "Other". I also wanted to highlight jobs that didn't succeed so I could quickly pick them out on the report. Therefore I used the expression

"= iif( Fields!last_run_outcome.Value=1,"Transparent", "Yellow")"

for the BackgroundColor property of the Last Run Outcome column. This results in a yellow highlighting of the job outcome if it is other than successful. You can see a sample Job Status report in the attached "Sample_Job_Status_Report.jpg" that includes an error.

I was using this report for quite a while to find jobs that didn't succeed and then using the SQL Server Agent Job history to see why jobs failed if they did. Then recently I saw Adam Aspin's article "Returning full error details from SQL Server Agent jobs". That article showed how to set the "Log to table" option for jobs and provided a stored procedure to get the full error details for a specific job that failed. See his article for details.

I decided that I could use that in conjunction with my job status report to give me an easy way to see the error message for failed jobs. I first created a report that provided the job error detail. It used his stored procedure as a data source and displayed fields in a non-columnar vertical format. In this case I added his Stored Procedure directly to the msdb database. When I upgrade to a new release I will have to verify if that stored procedure is migrated or not. I again used the sa user ID because of problems getting another ID to produce results.

I then added a column in the original Job Status report right after the Last Run Outcome column that I would use as a hyperlink to the report to show the detail for the failed job. I used a value of "+" for this field. However, I didn't want the + value to show up unless that job failed. So I set the Visibility, Hidden property value to

"= iif( Fields!last_run_outcome.Value=1,True, False)"

so the value was hidden if the job succeeded. To set the hyperlink you need to right click on the field in the report and select the properties. In the Navigation tab you will see an option to "Jump to report:". When you click this option you can select the report to jump to (in this case the Failed Jobs Log report) and you can supply any parameters needed by the report (in this case the job name). The images below show you the important settings for the + field that links to the Failed Jobs Log Report

So now if a job fails the job outcome value will be highlighted and have a + sign behind it also highlighted. If you click on the + sign it will then display the error details for that job. This gives me the full error details and eliminates the need to go in to the SQL Server Agent job history to find that information (which in most cases won't actually be the full output). . You can see a sample Job Status report in the image above. You can see a portion of a Job Error report in the images below:

I created a subscription to run this report each morning before I arrive at work and include the report in web archive format. So when I arrive at work I have a report in my e-mail that shows me the outcome of all the scheduled jobs.

The attached zip file contains a Visual Studio 2005 project with the two report rdl files and the data source rds files. I've also included sql files for creating the two stored procedures.

Using a combination of system stored procedures, Adam Aspin's stored procedure and two SQL Server Reporting Services reports I can now quickly see what jobs ran last night and which of those jobs failed.