Dooh PowerShell Trick–Running Scripts That has Posh Jobs on a SQL Agent Job

Yeahh Guys, It is my new d´ooh moment. I have a script that it is called by SQL Server Agent and in this scripts I am working with a LOT of PowerShell Jobs. For some reason, running on Posh console worked fine and by Schedule anything happens. Even a error message on Jobs History.

PS – I am using CMDexec Job Type  calling PowerShell.exe

I have a process that get all the windows updates applied in all servers in the last 24 hrs and stored into a SQL Server Table. The code is :

   1: Get-Content c:\temp\Servers.txt | % {

   2:     Start-Job -Name $_ -InitializationScript  {Ipmo Functions -Force -DisableNameChecking} `

   3:     -scriptblock {     (Get-WindowsUpdates -ComputerName $args[0] `

   4:                     | Where {$_.UpdateDate -ge ((Get-Date).adddays(-1))} `

   5:                     | select     CurrentDate, `

   6:                                 ServerName, `

   7:                                 UpdateClientApplicationID, `

   8:                                 UpdateDate,`

   9:                                 UpdateDescription, `

  10:                                 UpdateTitle) `

  11:                     | Out-DataTable `

  12:                     | Write-DataTable     -ServerInstance R2D2 `

  13:                                         -Database SQLServerRepository -TableName tbl_WindowsPatches

  14:                 } -ArgumentList $_

  15: }

My first test. Create a .bat and run  :

   1: powershell.exe "C:\Temp\Automation\GetWindowsUpdates.ps1" 

Ok.. what happens ? It creates some jobs and close the posh session and

nothing was stored.

Then I put in the .bat  the parameter –noexit to not close the posh session.

   1: powershell.exe –noexit "C:\Temp\Automation\GetWindowsUpdates.ps1" 

And it works. Why ? Because the PowerShell job run in another runspace, but at the same session that was called. The session cannot be closed until all posh jobs finishes.

So what I need to do ? Only have to wait to finish all  Posh Jobs   

   1: Get-Job | Wait-Job | Out-Null 

   2: Remove-Job -State Completed 

   1: Get-Content c:\temp\Servers.txt | % {

   2:     Start-Job -Name $_ -InitializationScript  {Ipmo Functions -Force -DisableNameChecking} `

   3:     -scriptblock {     (Get-WindowsUpdates -ComputerName $args[0] `

   4:                     | Where {$_.UpdateDate -ge ((Get-Date).adddays(-1))} `

   5:                     | select     CurrentDate, `

   6:                                 ServerName, `

   7:                                 UpdateClientApplicationID, `

   8:                                 UpdateDate,`

   9:                                 UpdateDescription, `

  10:                                 UpdateTitle) `

  11:                     | Out-DataTable `

  12:                     | Write-DataTable     -ServerInstance R2D2 `

  13:                                         -Database SQLServerRepository -TableName tbl_WindowsPatches

  14:                 } -ArgumentList $_

  15: }

  16:  

  17: Get-Job | Wait-Job | Out-Null 

  18: Remove-Job -State Completed 

The Command at SQL Agent Step  :

   1: powershell.exe "C:\Temp\Automation\GetWindowsUpdates.ps1" 

#PowerShellLifeStyle