Welcome, Guest: Join Kenyans247 Log In!

Stats: 290 Members, 13,218 topics and 65 posts Date:  Thursday 22 October 2020 at 10:02 AM
Popular & Trending NewestFeatured Links ClassifiedDirectoryLivescoresLive Kenyan TVLive Kenyan Radio Follow Us Like Us

About Us Terms & Conditions Privacy Policy FAQ's Disclaimer Copyright Contact Us Advertise With Us Download K247 APP Marketplace Moderator Policy We Are Hiring Kenya Radio and TV Stations Listen and Watch Live Letter From the C.E.O Kenya Radio Stations Live Kenya TV Stations Live Kenya - Newspapers and News Sites and popular blog Forum Rules

Sending Automated Job Email Notifications in SQL Server with SMTP

Kenyans247 / Science/Technology / Programing / Sending Automated Job Email Notifications in SQL Server with SMTP

199 people viewed

What Data Does Android Back Up Automatically? Optimizing Automated Rules In Google Ads Install Ubuntu Applications Easily With Automatix Apc Governors Demand Automatic Tickets For Obaseki, Akeredolu
(Go Down) (» Reply topic)
Sending Automated Job Email Notifications in SQL Server with SMTP by Kenyans247(1): Sun 22, March, 2020 10:11am
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.
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:
kenyans247
Click on the Steps tab, and you should see a screen that looks like this:
kenyans247
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’
kenyans247
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:
kenyans247
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:
kenyans247
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:
kenyans247

0 Shares 0 Like •

(» Go Up)

You must Sign in or Sign Up to post content

For you to post content on this website you have to log in or register, it only take few minute to create account

Go Up

Recommended for you

How To Stop Apple Music From Downloading Tracks Automatically So You Can Save Storage Space Apc Governors Demand Automatic Tickets For Obaseki, Akeredolu You Should Disable Automatic Downloads In Chrome Right Now Android Automatically Deletes Content Of My Downloads Folder

Currently: 1 guest(s) reading this article

Sections: Africa, Family, Romance, Environment, Autos, Art, Graphics & Video, Technology Market, Education, Innovation, Universities, K247 TV, Literature, Gossip, Events, Lifestyle, Ugandan Forum, Burundi Forum, South Sudan Forum, Somalia Forum, Rwanda Forum,

About Us | Terms & Conditions | Privacy Policy | FAQ's | Disclaimer | Copyright | Contact Us | Advertise With Us | Download K247 APP | Marketplace | Moderator Policy | We Are Hiring | Kenya Radio and TV Stations Listen and Watch Live | Letter From the C.E.O | Kenya Radio Stations Live | Kenya TV Stations Live | Kenya - Newspapers and News Sites and popular blog | Forum Rules | Featured LinksFollow Us on TwitterLike Us on Facebook

Kenyans247 - Copyright © 2019 - 2020 Sande Kennedy. All rights reserved. See How To Advertise.
Disclaimer: Every Kenyans247 member is solely responsible for anything that he/she posts or uploads on Kenyans247.