• ARTICLES
SEARCH

How-To Geek

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.

Download the sp_SQLNotify Stored Procedure here.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 11/2/06

Comments (41)

  1. Deepak shivnani

    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

  2. Deepak shivnani

    Thanks its working now.

  3. Samantha

    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.

  4. Varsha Chandra

    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?

  5. Ahmed

    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

  6. Ryan Broussard

    Awesome. Works Great.

  7. Ganesh Kumar

    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.

  8. Archana

    Thank you so much… solved a BIG problem for me…

  9. Erik

    Hi.
    Thank you very much, it’s a great help for me.

  10. Kerry

    Hi,

    Thanks for this it worked a treat!

    As a newbie geek I need all the help I can get.

    Thanks

    Kez

  11. jfvassar

    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.

  12. jfvassar

    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? :-)

  13. This stored procedure works on test 2 servers and it worked but when I try it on the production server it says:

    The server rejected one or more recipient addresses. The server response was: 550 5.7.1 Unable to relay for myemail@myorganization.com

    this isn’t a help request although I will post one in the forum this is just so people with the same problem will know that they are not alone

  14. Tony

    i tried the script with exchange 2003 and it worked fine. however, after upgrading to exchange 2007, the script fails with the following error

    ‘-2147220973

    (1 row(s) affected)

    Source: CDO.Message.1
    Description: The transport failed to connect to the server.’.
    Any help would be appreciated.

  15. Chetan Garude

    You might want to include in this post that the “OLE Automation” feature in Surface Area Configuration features must be turned on.

  16. Mitul

    Hi,

    For sending a mail through SQL Server its a required to install IIS and SMTP Server.

    Thanks & Regards
    Mitul

  17. Allwin

    Hi,

    Im able to run successfully. But im getting the mail twice.

    Please correct me

    Thanks,
    Allwin

  18. MissPeach

    Hi
    I have a questions…not related to the above.

    I am trying to find a relatively easy solution to automated email notifications sent out on certain dates. Here is the bg:

    My subscribers that are stored in my database have a choice to subsdribe to a service and I would to be able to offer them an option where they can receive automated reminder emails about this service on a date that they put in when signing up with this service.

    What would be the best way and easiest if possible to go about this…what progr. would you set it up in etc…any advice?

    Alexandra

  19. Jeremy

    great script, but as a SQL noob I can’t get it to work. It’s just not sending me e-mails when the job fails or succeeds. I have the address configured with the FQDN of our Exchange server as opposed to the IP address… any thoughts?

  20. Bridgett

    Hi

    I have a question regarding a automatic response on a job application status. I was send to Two email Reponses on a job application status for the same position which I had applied. One email was generated at the time of 1745 for eligible and other email generated 1749 ineligible. Keep in mind, I only submit my application on line one time. Why would I recieved two email responses and how could I challenge the error to Human Resources Department. I am quite sure they are getting overwhelmed with application on line.

  21. Bridgett

    Hi

    I have a question regarding a automatic response on a job application status. I was send to Two email Reponses on a job application status for the same position which I had applied. One email was generated at the time of 1745 for eligible and other email generated 1749 ineligible. Keep in mind, I only submit my application on line one time. Why would I recieved two email responses and how could I challenge the error to Human Resources Department. I am quite sure they are getting overwhelmed with application on line.

  22. soma

    I can send English email using this. What about Arabic body? I tried..but I am getting ???? chars in receiver email content.Does it support only English?
    Please advice.

  23. Marcus

    Send me notifications

  24. Lynda

    I have the smae problem with Tony:
    -2147220973
    Source: CDO.Message.1
    Description: The transport failed to connect to the server.
    Mail Sent!
    Of course I never recived the mail.
    Can anyone help me on this?
    Best regards
    Lynda

  25. Rohidas

    EXEC @hr = sp_OACreate
    EXEC @hr = sp_OASetProperty
    EXEC @hr = sp_OASetProperty
    EXEC @hr = sp_OASetProperty
    EXEC @hr = sp_OASetProperty
    EXEC @hr = sp_OASetProperty

    Not find related stored procedure in sql server 2008

  26. hunart

    Does anyone know if this stored precedure will work with SQL 2008 STD Edition?

    Thanks,
    HT

  27. Tito

    Very nice solution!
    It worked fine for me after I enabled Ole Automation procedures.

    EXEC sp_configure ‘show advanced options’, 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sp_configure ‘Ole Automation Procedures’, 1
    RECONFIGURE WI OVERRIDE
    GO

  28. Ludvick Rivero

    Hi, i works with SQLServer 2008, so i used de sp_send_dbmail instead of the stored post here. Everything works ok and i received the sucess and failure notification.

    Best Regards,
    Ludvick

    For the failure i wrote.
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘Desarrollo’,
    @recipients = ‘admin@desarrollo.com’,
    @subject = ‘Backup Job Failure’,
    @body = ‘The Backup Job Failed’,
    @body_format = ‘HTML’ ;

    For the success i wrote.
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘Desarrollo’,
    @recipients = ‘admin@desarrollo.com’,
    @subject = ‘Backup Job Success’,
    @body = ‘The Backup Job completed successfully’,
    @body_format = ‘HTML’ ;

  29. SHekar

    EXEC @hr = sp_OACreate
    EXEC @hr = sp_OASetProperty
    EXEC @hr = sp_OAMethod
    EXEC @hr = sp_OAGetErrorInfo
    EXEC @hr = sp_OADestroy
    sp_OAGetErrorInfo

    i could not find these stored procedures in sql server2005.
    can anyone please send me the scripts of these stored procedures?
    i need urgently

    thanks in advance

    Regards,
    SHekar

  30. balaram

    Hi all

    I need to send a mail from server to ‘hfdiuf@gamail.com’ and inaddition to it I want to include the DB statistics like “df -h” ,”vmstat ” and other things in that mail. Please some one help me on this.
    Pleaseeee reply to mail Id (balarami.dwaram-v@retail.adityabirla.com).

    SMTP configuration is done in that server .

    Thanks In Advance …

    Regards
    Balarami

  31. Raphael Saccomani - Deus te amo!

    how to authenticate a user with smtp server?

  32. Ankith Kumar

    Hi,

    I have a same kinda task to be done in my project. I need to run a procedure that runs automatically and sends mail to all those email addresses stored in my database. I tried to understand this article, but i dint understand how can i use this procedure in my way. Please help me regarding this.

    please mail me at: ankithkumarpatel@hlinesoft.com

    Thanks in advance.

  33. Ankith

    Hi Frnds,

    sorry, my above mail is not working. can you please reach me at ankith83@gmail.com

    I have a same kinda task to be done in my project. I need to run a procedure that runs automatically and sends mail to all those email addresses stored in my database. I tried to understand this article, but i dint understand how can i use this procedure in my way. Please help me regarding this.

    please mail me at:ankith83@gmail.com

    Thank you guys…

  34. Raigan

    First of all thanks for doing the interview. Awesome blog for inspiration. And second thing, I just love his work. Thanks for sharing.

  35. Saqib

    To SMTP authenticate add/change the following.

    Alter PROCEDURE [dbo].[sp_SQLNotify]
    @From varchar(100) ,
    @To varchar(100) ,
    @Subject varchar(100)=”",
    @Body varchar(4000) = “”,
    @SMTPserver varchar(100)=”",
    @SMTPuserName varchar(100)=”",
    @SMTPpassword varchar(100)=”"

    ok the system does not allow me to post if I have http link.

    so add the following for CDO configuration and turn on the OLE automation from the surface area configuration for SQL.

    smtpserver
    smtpserverport –set the value to ’25′ or ’587′
    sendusername
    smtpaccountname
    sendpassword
    smtpauthenticate –set the value to ’1′

  36. Stubb4Prez

    We have been using this method for a few years but we have one problem. Every couple months we stop receiving the emails. We are using SQL Server 2005 running on a Server 2003 box. Our SMTP server is also Server 2003. The only way to get emails again is to bounce the server? This obviously is not an acceptable solution. Does anyone have any recommendations?

    Thanks,

  37. Mamta

    Hi,

    I tired this and am not getting the email. Could you please help me out. I set out the stored proc as you mentioned.

    Thanks,

    Amit

  38. AlexPardo

    I recommend you to use the “Database Mail” (Sql Server 2005 and later) witch uses SMTP to send and e-mail. It has a graphic wizard for configuration and works like a charm. And when you want to send an email just do this:

    USE msdb
    GO
    EXEC sp_send_dbmail @profile_name=’Your_Profile’,
    @recipients=’example@mail.com’,
    @subject=’Test message’,
    @body=’Test Body’

  39. giftcp

    This worked for me first time. It is a great solution

  40. Nancy

    I have used this procedure and everything works fine.But still I am not getting the mail on the success of the Job. Please advise if I need to change anything?

    Thanks heaps,

  41. Kobena

    Hi all,

    i am looking at sending a file attachment with an sql extension which can be used to restore the database. I am using sql 2008 standard edition.

    Can anyone help?

    Thanks

Enter Your Email Here to Get Access for Free:

Go check your email!