Sending Automated Job Email Notifications in SQL Server 2005 with SMTP

Sending Automated Job Email Notifications in SQL Server 2005 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.