job that sends notification based on query result

Question

job that sends notification based on query result

tksmith

SSC Enthusiast

Points: 115
More actions
September 24, 2008 at 2:17 am

#198703

basicly i want to run a job once every 5 minutes that sends an email to an operators list if the result is true or false. the only way i can really see the sql server agent being able to do this is to somehow force a fail and have it email on fail, or is there a smipler way?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Naginder Pathania Right there with Babe Points: 722 More actions · Answer 1

Hi First You have to configure database mail in Management folder after that create a new job in agent , there you will find different options

such as step, schedule,opretaor and notification. which one you can use according to your purpose.

Production DBA

Naginder Pathania

tksmith SSC Enthusiast Points: 115 More actions · Answer 2

already doing all of the above. the problem is that a job only has the option to send an email on the following conditions - success, failure or completion. it can't do it based on the results of a query.

I have reporting services installed as well and it's subscription model can't do it either...

Naginder Pathania Right there with Babe Points: 722 More actions · Answer 3

clearly write ur issue and situation . So that i can tell u about exact solution or what do want to capture or process in ur job. you can handle these system stored procs.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

You can use the following SP sp_send_dbmail inside your job/query to send a mail. If you wrap this inside an IF statement the mail is sent when a condition is true (or false, depending on your needs).

Look at BOL for complete syntax.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

tksmith SSC Enthusiast Points: 115 More actions · Answer 5

thanks hanshi, works perfectly. there wouldn't happen to be a method that sends a popup via net send would there?

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 6

Try defining an operator that only receives notifications using net send. Then, use sp_notify_operator to send a notification to that operator.

I haven't tested it myself - but, it should work.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs