How to send IM text when a SQL job fails

/* this is very useful for critical production jobs that may fail at times when you do not have access to your email, but can be contacted via SMS Text Messaging on your CellPhone*/

set nocount on
declare @message varchar(555)
set @message = 'Process failed in job xyz'

-- different providers have different addresses to send SMS to cell phone --
/* T-Mobile: phonenumber@tmomail.net
Virgin Mobile: phonenumber@vmobl.com
Cingular: phonenumber@cingularme.com
Sprint: phonenumber@messaging.sprintpcs.com
Verizon: phonenumber@vtext.com
Nextel: phonenumber@messaging.nextel.com
where phonenumber = your 10 digit phone number */

exec master.dbo.xp_sendmail @recipients = '5555551212@messaging.sprintpcs.com' , @message = @message , @subject = 'Svr263 Job Failure'

Job Notification Random Features

If you've worked with job notifications in the past then you probably already know this, but I thought it was worth discussing. Any time you change the name of the operator you want to notify, the event that the operator is notified upon defaults back to 'On Success'.

There are three different events that will generate notification from the job properties:

• When the Job Completes - generates whether the job fails or completes
• When the job Fails - generates notification only upon failure
• When the job Succeeds - generates only upon job success

The problem comes in that intermittently when you change the operator to be notified, the event will default back to 'When the job Fails'. This is a great problem in that you would not be notified in the event of job success.