2010-10-11

SQL PowerShell output

There are several CmdLets for output in PowerShell. I got some by the statements
man write-*
and
man out-*

Putting the CmdLets in a script file
$DebugPreference = "Continue"

":: (default)"
":: Out-Null" | Out-Null
":: Out-Default" | Out-Default
":: Out-Host" | Out-Host
#":: Out-File"
#":: Out-Printer"
":: Out-String" | Out-String

Write-Host ":: Write-Host"
#Write-Progress ":: Write-Progress"
Write-Debug ":: Write-Debug"
Write-Verbose ":: Write-Verbose"
Write-Warning ":: Write-Warning"
Write-Error ":: Write-Error"
Write-Output ":: Write-Output"

I have disabled the calls of the CmdLets Out-File, Out-Printer and Write-Progress as I find them irrelevant to this little experiment.

Executing the script file gives in PowerShell (powershell.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

:: Write-Host
DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output


and in SQL PowerShell (sqlps.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output

In the script for SQL PowerShell I removed the call to Write-Host as is fails with
A job step received an error at line 18 in a PowerShell script. The corresponding line is 'Write-Host ":: Write-Host"'. 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.

When I execute the script in a PowerShell SQL Agent job step using the Invoke operator (& "H:\Script Library\Output.ps1"), the output file contains
:: (default)
:: Out-String

:: Write-Output

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 1 in a PowerShell script. The corresponding line is '& "H:\Script Library\Output.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: ':: Write-Error
'


I was a little surprised to see that the Debug Preference and the CmdLets Out-Default and Out-Host were ignored.
A quick conclusion is that the implicit pipe and the CmdLet Write-Output is general usable.

No comments: