SQL Server Agent Job Email Notifications via PowerShell

By:   |   Comments (4)   |   Related: > SQL Server Agent


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.


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 [email protected] 
  -From [email protected] -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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 22, 2015 - 2:11:09 PM - Aaron Back To Top (36998)

Can a parameter or identifying value of some sort be passed through the powershell script into the e-mail message (or title)?


Friday, December 28, 2012 - 9:39:25 AM - K. Brian Kelley Back To Top (21172)

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.

Friday, December 28, 2012 - 2:12:26 AM - PLahanov Back To Top (21167)

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




Thursday, December 27, 2012 - 12:02:50 PM - CodePro Back To Top (21160)

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

get free sql tips
agree to terms