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, which you can access at http://msdn2.microsoft.com/en-us/library/ms175575.aspx.)
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.