Send an email when scheduled job fails

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Send an email when scheduled job fails

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-04 : 23:16:56

I'd like a nightly scheduled job to email me if it ever fails.

My hosting provider says I need frontpage extensions installed on my server.

Is that correct?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-04 : 23:52:52

No.

Your host smokes a mighty fine variety of crack. You may suggest they read Books Online for a few minutes should they ever put the pipe down. Tell 'em to look under "jobs" and "notifications".

JohnDeere
Posting Yak Master

191 Posts

Posted - 2004-03-05 : 00:22:27

You will need to configure sql mail for the sql agent.

Lance Harra

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-05 : 08:10:59

OK and thanks. I'll revisit this with my provider today.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-05 : 08:38:37

Maybe I'm the crack smoker here. I couldn't find the original email so I asked the question again today.

The response was:

quote:
We need to enable the SQL mail service and install Outlook on the server.

This seems like a lot of software to send a single email when a job fails. I've got a stored procedure that'll send an email already. If that works, I'm not sure I want to add this additional baggage.

Sam

loiter99
Starting Member

38 Posts

Posted - 2004-03-05 : 08:39:23

If you don't have SQL mail config'ed, you can use this script:

-----------------------------------------------------------
declare @SMTP sysname, @FromRecipient sysname, @ToRecipient sysname
set @SMTP = (IP for SMTP goes here)
set @ToRecipient = (your e-mail address goes here)

exec master.dbo.xp_smtp_sendmail
@from = 'whoever',
@to = @ToRecipient,
@server = @SMTP,
@subject = 'Whatever you want',
@message = 'long message of some sort - hey dude, your job failed',
@attachments = 'drive:\folder\logerror.txt'

All you need to make this work is the IP address from your host for their SMTP. It all depends if they give that sort of info out to their users.

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-05 : 08:43:55

Yep. That's what my stored proc does when everything is running cookies and I place the call with a conditional statement.

In this case, I've got a JOB running. Only if it fails, do I want an email. There's a dialogue box to set this up in the job properties, but it refuses to configure. I'm trying to find the minimum weight-gain on my server to get it running.

I'm not clear that I could apply the code you mentioned when the job has failed. Maybe by linking to another job step containing that code on failure?

Sam

loiter99
Starting Member

38 Posts

Posted - 2004-03-05 : 10:46:37

Yea, just put that code in the next step after what ever you are trying to do.

Step 1 run code
* in advanced section: Got to step 3 on success, step 2 on failure

Step 2 send e-mail that step 1 failed

Step 3 life moves on

good luck

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-05 : 11:02:39

Ok. So it'll work and avoid installing new software.

Am I wimping out by not going for the SQL and Outlook mail extensions on my server?

Sam

loiter99
Starting Member

38 Posts

Posted - 2004-03-05 : 11:35:06

SQL Mail can be a blessing and a curse, I had one server it installed with no problems, and another where I could never get it to work. If you know how to use that script, you don't need to install SQL mail unless you want to e-mail queries, for which I have never had a need.

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-05 : 11:56:50

I don't have any need to email a query. I suppose you meant email a recordset / result of a query? I don't have any need for that either.

So I'll have nothing to be ashamed of if I go this route.

Where's Brett? He's always got something to say about anything.

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-05 : 11:58:39

Yukon is hopefully going to cure all your problems