solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

SQL Server Agent Job Email Notifications via PowerShell

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > SQL Server Agent
Problem

I want to be able to be notified by email when I have a SQL Server Agent job fail. However, I've been told that for whatever reason, I cannot use Database Mail or SQL Mail. Do I have any other options?  Check out this tip to learn more.

Solution

In fact, you do. With PowerShell you can create and send an email message even if you don't have the option of using Database Mail or SQL Mail. I know that I've run into cases where some folks don't want to turn on Database Mail because they are afraid users of the SQL Server will use it to send messages via an unapproved means. While this fear is unfounded, and Database Mail profiles can be easily protected, sometimes as a DBA you won't win this battle. If that's the case, then it's time to turn to PowerShell.

A SQL Server Agent Job Step Only for Failure Purposes

Here's the trick to ensure you're notified: build a step in the job you want notification on that is only used to send email in the event of a failure. For instance, step 3 in this job plan:

SQL Server Agent Job Steps

For every step except what would normally be the last step, you'll want to toggle the On success action: to go to the next job step. You'll also want to toggle the On failure action: option to go to the notification step. If you're not familiar with where this is, this is on the Advanced tab for the step.

Advanced Tab for a SQL Server Agent Job Step

For that last job step, ensure that you set the On success action: to be Quit the job reporting success. Otherwise, the job will continue on to the notification step. Do note that the On failure action: points to the notification step.

SQL Server Agent Job Step On Success Action and On Failure Action

You're notification step should be configured to either be a PowerShell or Operating System (CmdExec) script. I've had better success with configuring the notification step as an Operating System step and then calling PowerShell explicitly:

SQL Server Agent Job Step to execute PowerShell to send an email

If you're wondering what the whole text is, here's the basic code (I've broken it into multiple lines for readability, but you'll want to all on one line):

Powershell -command "& {send-mailmessage -To DBAAlert@mycompany.com 
  -From SQLServer@mycompany.com -subject 'Job Failure: Test Restore Failed' 
  -body 'SQL Server Agent job failed.' -SmtpServer smtp.mycompany.com}"

The notification step will also make use of the Advanced tab. We'll leave the On failure action: to the default, which is to Quit the job reporting failure. Because we want the overall job to register as having failed, we'll also configure the On success action: to Quit the job reporting failure. Because the job steps got to the notification step, that means something in the job failed. That's why we change the On success action: option.

Notification Settings for Email Step

The Magic of Send-MailMessage

The PowerShell cmdlet Send-MailMessage will do all the work for us. It provides the parameters we need: -To, -From, -Subject, -Body, and -SmtpServer. There are other flags you can use. To see them, start a PowerShell prompt and enter help Send-MailMessage.

If you're trying to pass in a single set of commands into PowerShell, you can do so by the following:

PowerShell -command "& { # Your Command Here }"

Do note that the curly braces are required. I've chosen the method where everything is enclosed in double quotes. If you do this and you want to make sure that this is executed as a command, you need to put the ampersand (&) symbol before the curly braces. Also, since we're using double quotes to encapsulate what PowerShell needs to execute, that means everywhere we'd normally use double quotes we'll need to change to single quotes. That's why you see 'Job Failure: Test Restore Failed' instead of "Job Failure: Test Restore Failed" for the subject.

Obviously, you'll need to know the SMTP server for your environment that you can use. You'd have to know this for Database Mail and most organizations have at least one in their environment for emails from systems. You may have to ask a system administrator or your mail administrator for the server. With that information as well as who you are sending it to and what message you want to send, you're all set. Obviously you can modify the subject and body to contain more specific information so that you know at a glance what job failed and even on what server.

Next Steps


Last Update: 12/27/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, December 27, 2012 - 12:02:50 PM - CodePro Read The Tip

Somebody insists that email be locked down, so they force you enable powershell instead. Brilliant.


Friday, December 28, 2012 - 2:12:26 AM - PLahanov Read The Tip

Really nice post. I too have faced some situation where I was restricted to use the Database mail.

Thanks

 

Plahanov


Friday, December 28, 2012 - 9:39:25 AM - K. Brian Kelley Read The Tip

CodePro, since PowerShell is now considered the administrative scripting language for Microsoft, I've seen cases where it's enabled for that reason but the powers that be deny Database Mail. This article actually comes from a couple of conversations I had with folks where this is the exact situation they faced. It makes me scratch my head, since Database Mail can be locked down, but sometimes we have to deal with folks who don't come to the same conclusion as we come to regarding a technology or feature.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.