Friday, October 26, 2012

Changing the operator on a job

So I was in the position today to change the operator on a bunch of SQL Agent jobs here. Since the server was very remote, I didn't want to clickety-clack through it. Not that I'd do it that way anyways, but either way, I used Powershell.


$s = new-object 'Microsoft.SqlServer.Management.Smo.Server' 'my-server'
$agent = $s.JobServer
$operator = $agent.Operators | where {$_.Name -eq 'DBA Alerts'}
$jobs = $agent.Jobs | where {$_.Category -eq 'Database Maintenance'}
foreach ($job in $jobs) {
   $job.OperatorToEmail = $operator.name
   $job.EmailLevel = 'OnFailure'
   $job.Alter()
}


I got tripped up a little bit on the $job.OperatorToEmail = $operator.name bit since I figured I could just assign the Operator object directly. Silly me! The OperatorToEmail takes a string and the ToString for the Operator class puts square brackets around the name. I like SMO in general, but sometimes the design decisions are... interesting. Anyways, enough of my ranting. Enjoy!

No comments:

Post a Comment