PowerShell in the SQL Agent with Verbose output
Trying to run PowerShell scripts from within SQL Server is fraught with danger it seems! what I thought would be a pretty simple, has actually got some issues! I realize some of the issues are my own doing (If I write a PowerShell script that needs to be automated but is nothing to do with SQL. My default response is to still throw it into an Agent Job, which isn’t what the Agent is for!) but if you like PowerShell and SQL Server you might get into the same situations as I have and find these solutions handy..
I have a script that needed running daily. I wanted to output to a file for troubleshooting the steps at a later stage.
In a first draft of the code I used Write-Host to detail when a step started , finished, and any logic going on during!
Problem One: Write-Host doesn’t work…
The corresponding line is ‘Write-Host “line 1″‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot invoke this function because the current host does not implement it. ‘. Process Exit Code -1. The step failed.
Considering I only wanted the info text to show up when I wanted to, Write-Host was a bad choice anyway. So its down to Write-Verbose
Problem Two: Write-Verbose
I blogged about using Write-Verbose in a script instead of a function before and what you need to do to get it working here: https://sqlnotesfromtheunderground.wordpress.com/2014/01/25/using-write-verbose-in-scripts-and-output-files/
to recap you need to add:
At the beginning of your script.
So, what’s the problem with Write-Verbose. Well if you run the script from within the command window you will get the error:
PowerShell script. The corresponding line is ‘Param()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘An expression was expected after ‘(‘. ‘. Process Exit Code -1. The step failed.
Hopefully some of you out there (Note that I didn’t as I ran this!) would relies you cant run the code in the command window as how would tell the script to run in Verbose mode or not!
So can we run a script with that code in, if it’s a script! well yes we can!.
Putting something like:
in your command window telling the PowerShell job to run the script works fine! but we want that lovely Verbose data! as I said in my last post to get the verbose information out we need to use
B:\s\test.ps1 -verbose 4> B:\s\results.txt
But now we get the following error:
PowerShell script. The corresponding line is ‘B:\s\test.ps1 -verbose 4> B:\s\results.txt’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘A positional parameter cannot be found that accepts argument ‘4>’. ‘. Process Exit Code -1. The step failed.
The solution, however strange you may find it (or obvious!) was to call PowerShell from the CmdExec Type agent job.
This then lets me run code without erroring and getting my Verbose information in the results.txt file. this Makes me happy! a problem most sensible people properly will never face! but a solution none the less I won’t have it taken away from me 🙂
The command would be like:
Run as Operating system (CmdExec)
powershell.exe B:\s\test.ps1 -verbose 4> B:\s\results.txt