Run SQL Jobs from PowerShell and Get Their Status

Here is one of those things...

You know that if you do a

Import-Module -Name "sqlps" -DisableNameChecking

and then create a SQL job object like this

$hostname = hostname
Set-Location "SQLSERVER:\sql\$hostname\default\jobserver\jobs"
$job = Get-ChildItem | Where-Object {$_.Name -eq "Test"}

you can run the job but it will not update its status in the same PowerShell session, even if you recreate the object.

$job.LastRunDate
$job.Start()
$job.LastRunDate

The LastRunDate field will remain the same, as will all other fields.

Here is how you can get the real status anyway.

$newlastrundate = Invoke-Command -ComputerName "localhost" -ScriptBlock {
    Import-Module -Name "sqlps" -DisableNameChecking | Out-Null
    $hostname = hostname
    Set-Location "SQLSERVER:\sql\$hostname\default\jobserver\jobs"
    $job = Get-ChildItem | Where-Object {$_.Name -eq "Test"}
    $job.LastRunDate
}

This will work, even if perhaps it isn't tbe brightest solution. But it does create a new PowerShell session. And boy shouldn't you use a kludge like this in a production environment!

 © Andrew Brehm 2016