Quick Tip. Outputting SQL Agent Job PowerShell in Job History


My good friend Sir Jonathan Allen (@fatherJack) asked me if has some way to output a SQL Agent PowerShell Job in the Job History.

The problem. He has a SQL Agent PowerShell Job to delete old files in a log shipping process and want to output  the files that were removed in the Job History

The tip is just to use write-output. The script below is just to illustrate :

   1: $FilesRemoved = 'Files Deleted : '

   2: gci "c:\test\*.*" | foreach {

   3:     $FilesRemoved += "Name: {0}, " -f $_.name

   4:     Remove-Item $_.fullname 

   5: }

   6: write-output $FilesRemoved

Then if you look at your Job History :

image

The trick to work with SQL Agent PowerShell Job, is that you can´t use :

   1: $FilesRemoved += "Name: $($_.name)"

Looks like $($_,name)  it is interpreted as some special char, see [Updated]..I need to do a research about it, since all my PowerShell Jobs are cmdexec calling a .ps1

So you need to use format specifier :

   1: "Name: {0}, " -f $_.name

or

   1: $FilesRemoved += "Name: $_.name"

I don’t  liked this way because it will show to you the full path from the file. IMHO it is not a friendly , loos like a “screen dirty” (don’t know how to say in English), since you know the path  from your files..just need the file name

 

[Updated] Our good friend and also a PowerSheller Kyle Neier (@Kyle_Neier)  just update me about the issue in the SQL Agent PowerShell Job,looks like it is related to SQL Agent Tokes. See the connect Item –> SQL Agent Job – PowerShell step giving escape token error

image

Thanks Man Sorriso

#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 Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to Quick Tip. Outputting SQL Agent Job PowerShell in Job History

  1. Pingback: PowerShell – Diversas « Alex Souza

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