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:
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.
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.
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:
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.
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
- Learn how to enable PowerShell on Windows Server 2008 R2 Core, if you’re using that OS.
- Start with an intro to PowerShell if you’re not very familiar with it.
- Understand how to set up Database Mail if that’s an option in your environment.
- See how to check for failed SQL Server Agent Jobs.
