When a SQL Server job fails, you can have SQL Server Agent send you a notification. However, as Figure 1 shows, the notification doesn't include the error message generated by that job failure. So, you have to connect to SQL Server to read the error message to determine whether the failure is being caused by a critical problem.
Because it's helpful to receive the error messages generated by failed jobs, I created a SQL Server job step that calls a stored procedure named spDBA_job_notification. The job step passes the failed job's ID to the stored procedure. The stored procedure uses the job ID to query the msdb agent tables for the most recent error message for that job. The stored procedure incorporates the error message into an email and sends the email to the specified person. Figure 2 shows an example of an email sent by the stored procedure.
To pass the job ID to the stored procedure from within the job, I use a SQL Server Agent token. SQL Server Agent lets you use tokens in T-SQL job step scripts. In SQL Server, various tokens represent job elements. For example, the SRVR token represents the server running SQL Server, the A-DBN token represents the name of the database running the job, and the JOBID token represents the job ID. (For a complete list of tokens, see the "Using Tokens in Job Steps" section in SQL Server 2005 Books Online—BOL.) When you insert a token in a job step script, SQL Server Agent replaces the token with the element it represents at run time.
You set up job steps in SQL Server Management Studio (SSMS). Open SSMS, expand SQL Server Agent (it must be running), then expand Jobs. Right-click the target job and select Properties. In the Job Properties dialog box, select Steps from the Select a page menu on the left. Figure 3 shows an example of the Job step list section that appears.
In Figure 3, note that there are two steps. In step 1, the job is run. In this case, the job is a T-SQL script named Simple select. (You can download SQLJob_Create.sql, which will create this test job, from the SQL Server Magazine Web site.) When the Simple select script runs without any problems, a completion status of success is logged. When the Simple select script encounters a problem that causes it to fail, step 2 executes.
Figure 4 shows an example of the settings for step 2. Because the spDBA_job_notification stored procedure resides in the master database, the Database dropdown list is set to master. If you're running SQL Server 2005 SP1 or later, the command for step 2 is
EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID))
If you're running a version earlier than SQL Server 2005 SP1, the command is
EXEC spDBA_job_ notification \[JOBID\]
This command calls the spDBA_job_notification stored procedure. The SQL Server Agent engine replaces the $(ESCAPE_NONE (JOBID)) or \[JOBID\] token with the job ID before executing the stored procedure. You can download spDBA_ job_notification.sql from the SQL Server Magazine Web site. To use this script, you need to have Database Mail set up. If it isn't set up, you can do so with DBMail_Setup.sql, which you can also download from the SQL Server Magazine Web site.
If you were to manually supply the job ID and execute spDBA_job_notification, the stored procedure wouldn't work. The stored procedure must be called from within a job step because it checks for the most recent job history without a completion status. When you run it in SSMS, the job is already completed and has a status of either success or failure.
If someone were to make changes to the stored procedure that would cause it to fail or if someone were to comment out the email section in the code, you wouldn't receive any notifications. For this reason, I highly recommend you also set up the job's Notifications page as a backup measure so that you get notified of job failures. You can access the Notifications page from the Job Properties dialog box.
