Sending Automated Job Email Notifications in SQL Server with SMTP
When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don't realize until your database crashes and you can't restore it since you don't have a current backup.
That's where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.
SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn't necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.
Download the sp_SQLNotify Stored Procedure here.
You will want to edit one line in the stored procedure to put the IP address of your SMTP server:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.1.1.10′
Install the stored procedure into the master database, so it can be easily used from wherever needed.
Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:
Click on the Steps tab, and you should see a screen that looks like this:
Click the New button to create a new job step. We will use this step to send the email notification on success.
Step Name: Email Notification Success
Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:
exec master.dbo.sp_SQLNotify 'server@localserver.com','admin@localserver.com','Backup Job Success','The Backup Job completed successfully'
Click OK and then click the New button again to create another step. This will be the failure notification step.
Step Name: Email Notification Failure
SQL:
exec master.dbo.sp_SQLNotify 'server@localserver.com','admin@localserver.com','Backup Job Failure,'The Backup Job failed'
Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:
What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.
Now edit the second step, the one labled "Email Notification Success", and set the properties as seen here:
We are saying that if the notification job is successful, then just quit the job without running step 3. If we don't specify this, then we will end up getting two emails, one with success and one with failure.
Now edit the third step, the one labled "Email notification failure", and set the properties as seen here:
Now your job steps should look like this:
You should now have email notifications in your inbox for either success or failure.
Note: The stored procedure used in this article was found here, although that may not be the original source.
Hi
I have used this procedure and everything works fine.But still I am not getting the mail on the success of the Job.SMTP server is also properly configure.Do I need to do any other changes.
Regards
Deepak
Thanks its working now.
Hi,
I have used this procedure and everything work fine. Same with Deepak Shivnani's experience, I am not getting the email on the success/failure of the job.
I try to browse to :-
"http://schemas.microsoft.com/cdo/configuration/sendusing"
"http://schemas.microsoft.com/cdo/configuration/smtpserver"
both page can not be found. Is this the reason why is it not working?
Please help. Thanks.
Hi,
I am getting this email notification failure every time job runs. the failure is due to the MAPI error: 285. Now how to resolve this error?
Hi
Could this be use to send an email notification with a log file attachment? If so please kindly elaborate as to how this can be achieved.
Regards
Ahmed
Awesome. Works Great.
Hi,
Please tell me clearly what changes have to be made?
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.1.1.10′
Do we need to change only this '10.1.1.10′ ip address part?
Thanks,
Ganesh Kumar.
Thank you so much… solved a BIG problem for me…
Hi.
Thank you very much, it's a great help for me.
Hi,
Thanks for this it worked a treat!
As a newbie geek I need all the help I can get.
Thanks
Kez
I too think this will be greatly useful for what I need, but also cannot seem to get it to work. I posted an "off topic" support question about it in the Forum, but just thought I would mention it here too.
Actually, I now have this working. Also, I did not remember (Homer Simpson doh! moment) that the sp_OA procedures are extended stored procedures in SQL 2000. The way I got this to work is that the person who gave me the SMTP server IP and DNS actually gave me an SMTP server (MS Exchange) that only allowed messages from certain trusted sources to transmit. One option was to try and get that SMTP administrator to accept incoming messages from the SQL host, but I found a faster (better) way: I asked one of the guys to send me a common e-mail message, then I looked all through the Header information from the e-mail, found a different Exchange server in the messages, then tried that server, and it worked! Magic, huh?