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 Sorriso  

doh1

   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

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Passatempos, SQL SERVER EM GERAL. Bookmark the permalink.

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

  1. Saulo Goes says:

    Graaande Laerte, me passa o seu e-mail… o meu msn é saulogoes@hotmail.com, mas uso mais o @gmail.com… segue o link que comentei com vc ontem: http://tweetfuel.stinkdigital.com … Abraço

  2. Pingback: SQL SERVER – Powershell – Importing CSV File Into Database – Video « SQL Server Journey with SQL Authority

  3. Felipe says:

    Olá, Laerte! Tudo bem? Poderia me ajudar caso possível?
    Tenho um script de PS que roda normalmente pela console, porém quando mando o Server Agent rodá-lo, não consigo… tambem tentei rodá-lo como voce explica aqui, por uma task cmd, porém, nada feito… (quando rodo direto executando a bat, ele funciona). Já passou por alguma situação assim? Teria alguma dica?
    Desde já, muito obrigado!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s