As you may or may not know, you can set up SQL Server Agent to email you whenever a job fails. This however requires SQL Mail to be properly working. Again, as you have probably experience this is a lot easier said than done. What I have ended up doing is using XPSMTP for sending email notifications to me whenever a job fails. This does require a little bit of extra work in the job definition. You must create a new step to be run only if that job step fails, this however isn't much of a problem if you don't have a lot of jobs defined. Below is the procedure that I use for sending out the email. This procedure requires you to send in the job_id, which you will need to pull out of the msdb.dbo.sysjobs[job_id]. For more information about the system tables Dave's last post.
/*
* BE SURE TO CHANGE THE MAIL SERVER
* AND EMAIL ADDRESSES TO THE ADDRESSES
* YOU WANT THE NOTIFICATIONS TO BE
* DELIVERED TO.
*
* IF YOU DO NOT CHANGE THE ADDRESSES
* YOU WILL END UP TELLING ME ALL OF YOUR
* DEEPEST DARKEST SECRETS!!!! ;)
*
*/
CREATE PROCEDURE dbo.sp_SendJobAlert
@job_id UNIQUEIDENTIFIER
AS
DECLARE @FROM NVARCHAR(4000),
@FROM_NAME NVARCHAR(4000),
@TO NVARCHAR(4000),
@Subject VARCHAR(4000),
@Message VARCHAR(4000),
@priority NVARCHAR(10),
@rc INT
SELECT TOP 1
@subject = '[' + hist.server + '] JOB FAILED: ' + hist.step_name,
@message = hist.message
FROM msdb.dbo.sysjobhistory hist
WHERE job_id = @job_id AND step_id <> 0 AND run_status = 0
ORDER BY instance_id DESC
SELECT @FROM = N'sql@aaronweiker.com',
@FROM_NAME = N'SQL Server',
@TO = N'alerts@aaronweiker.com',
@priority = N'HIGH'
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = @FROM,
@TO = @TO,
@priority = @priority,
@subject = @subject,
@message = @message,
@server = N'mail.aaronweiker.com
RETURN @rc