CODEWITHSUNDEEP
sqlsql-serverpowershell
DarrenS
DarrenS

Reputation: 80

How to edit SQL job step using PowerShell

Am looking to connect and loop through hundreds of SQL servers/instances, to find a specific job, with a specific job step, and looking to modify the command inside that job step to change a 1 to 0 - and I wish to use PowerShell to do this.

I have a PS script to enable or disable an entire Job, but I don't want to disable the job or even the job step. I want to change the syntax inside the step, eg. from @ENABLED=1 to @ENABLED=0

I need to target these SQL servers by IP address with specific SQL permissions (eg. sa account) as the servers are not on a domain (VMs inside vCloud Director with NAT'ed IPs)

SQL Server 2014 w\SP1 PowerShell v5 Windows Server 2012 R2 & 2008 R2

Thanks in advance Darren

Upvotes: 2

Views: 1588

Answers (1)

Bruce
Bruce

Reputation: 1623

Using SMO

  1. Define the new job step SQL. You could do this anywhere before the next to last line.
  2. Load the SMO assembly.
  3. Create your server object
  4. Assuming the job name and step name you're looking for are testJob3 and secondStep this line shows the current command text. It's not necessary for what you want to do.
  5. Change the job step command text
  6. Commit the change.
$newCmd = "DECLARE @ENABLED`nSET @ENABLED = 0`n-- This is the end"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$srvObj = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $connectToThisServer
$srvObj.JobServer.Jobs['testJob3'].JobSteps['secondStep'].Command
$srvObj.JobServer.Jobs['testJob3'].JobSteps['secondStep'].Command = $newCmd
$srvObj.JobServer.Jobs['testJob3'].JobSteps['secondStep'].Alter()

Upvotes: 2

Related Questions