November 30, 2012 at 3:41 am
A general question that I'm struggling to find an answer to (2008 R2).
Once I've got things clear in my mind, the plan is to use db mail to send an email (custom subject, body, perhaps with query results) to a number of recipients (emails stored in a table), based on a business rule. It will be a job run daily.
So far - though this could be wrong(!):
Alerts are for predefined events that go in the Windows application log.
Job notifications only cover success or failure of the job.
In which case, I need to write my stored procedure for the job to include T-SQL for exec sp_send_mail?
Thanks if you can point me in the right direction before I take a wrong turning.
November 30, 2012 at 3:47 am
Alerts in SQL can be for a number of things, either a SQL Server Event, a WMI Event or a SQL Performance Condition event, they notify you of any alerts which trigger.
Job notifications do as you say, they notify should a job fail, complete, or succeed.
If what you want isn't covered by a SQL alert or a job notification, then all you need to do is as you say, build a query up which incorportates sp_send_dbmail, which will send a mail out to the user(s).
November 30, 2012 at 3:59 am
Thanks for the confirmation. I do find the terminology confusing: alerts notify you of events, and jobs have notification on the outcome of running it.
November 30, 2012 at 4:05 am
Yeah it is confusing at first.
Both Agent Alerts and Job Notifications use the same behind the covers methods of letting you know something has happened.
They both execute sp_notify_operator which calles sp_send_dbmail to do the mail send.
So in effect, they are both in effect Notifications, its just remembering the differences between the two and what you can setup as an AlertNotification and what you can setup as a JobNotification.
But for what you want to do it would just be a simple case of using sp_send_dbmail in your procedure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply