Add SQL Server Job Failure Notifications with PowerShell

A client recently asked me if I could add failure notifications to the SQL Server Agent jobs running in their environment. Now, as you could probably guess, there can be a lot of jobs in any normal business environment.

In December of last year, I showed you how to script the setup of Database Mail for SQL Server 2005 and up. For this task I, used that script to start, followed by additional steps to add failure notifications to all the jobs on a server.

Once Database Mail is set up, you can connect to the server's JobServer object, which is where SQL Server Agent jobs live.

# SQL 2005 and up$js = $svr.JobServer

You need to set the notification type for all Agent jobs to use Database Mail, and so you'll set the job server's AgentMailType property to use Database Mail.

$js.AgentMailType = [Microsoft.SqlServer.Management.Smo.Agent.AgentMailType]::DatabaseMail$js.DatabaseMailProfile = 'DBAAlertsMail'$js.Alter()

You'll also need an operator, who'll receive the alerts. You'll create a new operator and set the email address property.

$oper = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator ($js, 'DBAAlerts')$oper.EmailAddress = '[email protected]'$oper.Create()

Now, all you have to do is iterate through the jobs and set failure notification to use the new operator. The job server has a Jobs collection, and the Count property tells you how many jobs there are. You cycle one by one through the jobs, set the OperatorToEmail property to the operator name, the EmailLevel property to the OnFailure action, and alter the job.

for ($i=0; $i -lt $js.Jobs.Count; $i++) {    $job = $js.Jobs[$i]    $job.OperatorToEmail = $oper.Name    $job.EmailLevel = [Microsoft.SqlServer.Management.SMO.Agent.CompletionAction]::OnFailure    $job.Alter()    }

For SQL Server 2005 and up, that's all you need to do. There are a lot of companies that are still running SQL Server 2000, though, and this client is no exception. Microsoft offered a feature called SQL Mail in that release, but it required you to install a MAPI client on the server, which had some negative security implications. Former Microsoft guru Gert Drapers created his own SMTP email feature called xp_smtp_sendmail (which he told me he'd written to be included in the product, but the powers-that-be shelved it). He then released it himself and made it available via his old website.

I tried (unsuccessfully) to find a reliable source for this invaluable tool, but kept a copy in my own archives for just times like this. The installation procedure involves copying the DLL to the BINN directory of SQL Server and registering the extended stored procedure. 

Once that was done, I then had to change the jobs to notify when failures occur. Because I can't use a built-in feature for the notification, the method I used with Database Mail wouldn't work. All of this company's jobs have a single step, so I just decided to add a new step to each job, set the existing step to 'succeed' if the step succeeds, and go to the new 'FailNotify' if the step fails. I then added the FailNotify step to send an email using Gert's extended stored procedure.

First, connect to the server, and set the $js variable to the server's JobServer object.

# For SQL 2000$svr = New-Object Microsoft.SqlServer.Management.Smo.Server MyServer$js = $svr.JobServer

Cycle through the jobs. For each job you want to grab the job name for the subject and email message body.

for ($i=0; $i -lt $js.Jobs.Count; $i++) {    $job = $js.Jobs[$i]    $jnm = $job.Name

Next, you create the T-SQL statements to set the subject and message variables, then call xp_smtp_sendmail with the required parameters. Use a here-string to make it easier to read.

    $cmd = @"DECLARE @subj varchar(100)DECLARE @mesg varchar(2000)select @subj = @@servername + ' - $jnm Failure'select @mesg = 'The $jnm job on ' + @@servername + ' failed at ' + convert(varchar(25), getdate(), 100)exec master.dbo.xp_smtp_sendmail@FROM                        = N'[email protected]',@FROM_NAME                = N'DBAAlerts',@TO                        = N'[email protected]',@priority                = N'NORMAL',@subject                = @subj,@message                = @mesg,@type                        = N'text/plain',@server                 = N'smtpsrv.example.com'"@

You need the count of job steps, and use one less than that as your index to the last step in the job. You'll load that JobStep object into the $jst variable for the time being.

$jsc = $job.JobSteps.Count    $jsu = $jsc - 1    $jst = $job.JobSteps[$jsu]

Now, you'll create a new step called FailNotify, using the TransactSql subsystem and set the T-SQL command we built earlier as the command for the step.

    $jsn = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($job, 'FailNotify')    $jsn.SubSystem = 'TransactSql'    $jsn.Command = $cmd

Since this is a failure step, you'll set both the OnSuccessAction and the OnFailAction to the QuitWithFailure setting, and create the step in the job.

    $jsn.OnSuccessAction = 'QuitWithFailure'     $jsn.OnFailAction = 'QuitWithFailure'     $jsn.Create()

Finally, you'll set the OnFailAction of the original final step to 'GoToStep,' and set the OnFailStep to one greater than the last step. Alter the step, and alter the job.

    $jst.OnFailAction = 'GoToStep'    $jst.OnFailStep = $jsc + 1    $jst.Alter()    $job.Alter()    }

Once this script cycles through the jobs on the server, every job will have a failure notification step which will send you email letting you know the job failed.