When creating my own backup management method across multiple MS-SQL servers, I spent a lot of time studying the mechanism of passing values in PowerShell during remote calls. Therefore, I'm writing a note for myself, in case it might be useful for someone else.
So, let's start with a simple script and run it locally:
$exitcode = $args[0]
Write-Host 'Out to host.'
Write-Output 'Out to output.'
Write-Host ('ExitCode: ' + $exitcode)
Write-Output $exitcode
$host.SetShouldExit($exitcode)For running scripts, I will use the following CMD file; I won't present it each time:
@Echo OFF
PowerShell .TestOutput1.ps1 1
ECHO ERRORLEVEL=%ERRORLEVEL%We will see the following on the screen:
Out to host.
Out to output.
ExitCode: 1
1
ERRORLEVEL=1
Now let's run the same script via WSMAN (remotely):
Invoke-Command -ComputerName . -ScriptBlock { & 'D:sqlagentTestOutput1.ps1' $args[0] } -ArgumentList $args[0]And here is the result:
Out to host.
Out to output.
ExitCode: 2
2
ERRORLEVEL=0Wonderful, the Errorlevel seems to have disappeared, but we need to get the value from the script! Let's try the following construction:
$res=Invoke-Command -ComputerName . -ScriptBlock { & 'D:sqlagentTestOutput1.ps1' $args[0] } -ArgumentList $args[0]Things get even more interesting. The entire output in Output has disappeared:
Out to host.
ExitCode: 2
ERRORLEVEL=0Now, as a lyrical digression, I want to note that if you use Write-Output inside a PowerShell function or simply an expression without assigning it to any variable (which implicitly implies output to the Output stream), nothing will be shown on the screen even during local execution! This is a consequence of the pipeline architecture of PowerShell — each function has its own Output pipeline, an array is created for it, and everything that gets into it is considered the result of the function's execution. The Return statement adds the returned value as the last element to this same pipeline and hands control back to the calling function. To illustrate, let’s execute the following script locally:
Function Write-Log {
Param( [Parameter(Mandatory=$false, ValueFromPipeline=$true)] [String[]] $OutString = "`r`n" )
Write-Output ("Function: "+$OutString)
Return "ReturnValue"
}
Write-Output ("Main: "+"ParameterValue")
$res = Write-Log "ParameterValue"
$res.GetType()
$res.Length
$res | Foreach-Object { Write-Host ("Main: "+$_) }
And here is its result:
Main: ParameterValue
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Object[] System.Array
2
Main: Function: ParameterValue
Main: ReturnValueThe main function (the body of the script) also has its own Output pipeline, and if we run the first script from CMD, redirecting the output to a file,
PowerShell .TestOutput1.ps1 1 > TestOutput1.txt
then we will see on the screen
ERRORLEVEL=1and in the file
Out to host.
Out to output.
ExitCode: 1
1
if we make a similar call from powershell
PS D:sqlagent> .TestOutput1.ps1 1 > TestOutput1.txtthen the screen will display
Out to host.
ExitCode: 1and in the file
Out to output.
1This happens because CMD runs powershell, which, in the absence of other instructions, mixes the two streams (Host and Output) and gives them to CMD, which sends everything it received to the file. In the case of running from powershell, these two streams exist separately, and the redirect symbol only affects Output.
Returning to the main topic, let's recall that the .NET object model exists fully within powershell on a single computer (one OS). When remotely executing code via WSMAN, object transfer occurs through XML serialization, which adds a lot of additional interest to our research. Let's continue our experiments by executing the following code:
$res=Invoke-Command -ComputerName . -ScriptBlock { 'D:sqlagentTestOutput1.ps1' $args[0] } -ArgumentList $args[0]
$res.GetType()
$host.SetShouldExit($res)
And here’s what we have on the screen:
Out to host.
ExitCode: 3
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Object[] System.Array
Cannot convert argument "exitCode" with value: "System.Object[]" to type "System.Int32" for "SetShouldExit": "Cannot convert value "System.Object[]" of type "System.Object[]" to type "System.Int32"."
D:sqlagentTestOutput3.ps1:3 line:1
+ $host.SetShouldExit($res)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
ERRORLEVEL=0A wonderful result! It means that when calling Invoke-Command, the division of pipelines into two streams (Host and Output) is preserved, giving us hope for success. Let's try to leave only one value in the Output stream by modifying the very first script that we run remotely:
$exitcode = $args[0]
Write-Host 'Out to host.'
#Write-Output 'Out to output.'
Write-Host ('ExitCode: ' + $exitcode)
Write-Output $exitcode
$host.SetShouldExit($exitcode)
Let's run it like this:
$res=Invoke-Command -ComputerName . -ScriptBlock { 'D:sqlagentTestOutput1.ps1' $args[0] } -ArgumentList $args[0]
$host.SetShouldExit($res)
and… YES, it seems this is a victory!
Out to host.
ExitCode: 4
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Int32 System.ValueType
ERRORLEVEL=4Let's try to understand what happened. We locally invoked PowerShell, which in turn called PowerShell on a remote computer and executed our script there. Two streams (Host and Output) from the remote machine were serialized and sent back, with the Output stream being converted to Int32 type if it contained a single numeric value, and this was passed to the receiving side, which used it as the exit code for the invoking PowerShell.
And as a final check, let's create on server an SQL job with a single step of type "Operating System (cmdexec)" with the following text:
PowerShell -NonInteractive -NoProfile "$res=Invoke-Command -ComputerName BACKUPSERVER -ConfigurationName SQLAgent -ScriptBlock {& 'D:sqlagentTestOutput1.ps1' 6}; $host.SetShouldExit($res)"HURRAY! The job completed with an error, log text:
Running as user: DOMAINagentuser. Out to host. ExitCode: 6. Process exit code 6. The step terminated with an error.
Conclusions:
- Avoid using Write-Output and specifying expressions without assignment. Remember that moving this code to another place in the script can lead to unexpected results.
- In scripts intended not for manual execution but for use in your automation mechanisms, especially for remote calls via WINRM, ensure proper error handling using Try/Catch, and ensure that under any circumstances this script sends exactly one primitive type value to the Output stream. If you want a classic Errorlevel, this value must be numeric.
Source: habr.com
