Get best of the week

Return value from powershell invoke-command to SQL-Server Agent

When creating my own backup management methodology on many MS-SQL servers, I spent a lot of time studying the mechanism of transferring values ​​to powershell for remote calls, so I am writing a memo to myself, and suddenly it will come in handy for someone else.

So, let's take a simple script to start with 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)

To run the scripts, I will use the following CMD file, I won’t give it every time:

@Echo OFF
PowerShell .\TestOutput1.ps1 1
ECHO ERRORLEVEL=%ERRORLEVEL%

On the screen we will see the following:

Out to host.
Out to output.
ExitCode: 1
1
ERRORLEVEL=1

Now run the same script through WSMAN (remotely):

Invoke-Command -ComputerName . -ScriptBlock { &'D:\sqlagent\TestOutput1.ps1' $args[0] } -ArgumentList $args[0]

And here is the result:

Out to host.
Out to output.
ExitCode: 2
2
ERRORLEVEL=0

Miraculously, Errorlevel disappeared somewhere, but we need to get the value from the script! We try the following construction:

$res=Invoke-Command -ComputerName . -ScriptBlock { &'D:\sqlagent\TestOutput1.ps1' $args[0] } -ArgumentList $args[0]

It’s even more interesting. The message output in Output disappeared somewhere:

Out to host.
ExitCode: 2
ERRORLEVEL=0

Now, as a lyrical digression, I note that if you write Write-Output or just an expression without assigning it to any variable inside the Powershell function (and this implicitly implies output to the Output channel), even if you run it locally, nothing will be displayed! This is a consequence of the powershell pipeline architecture - 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, the Return statement adds the return value to the same pipeline as the last element and transfers control to the calling function. To illustrate, 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 his result:

Main: ParameterValue

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array
2
Main: Function: ParameterValue
Main: ReturnValue

The main function (the script body) 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 on the screen we will see

ERRORLEVEL=1

and 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.txt


then the screen will be

Out to host.
ExitCode: 1

and in the file

Out to output.
1

This is because CMD starts 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, and if launched from powershell, these two streams exist separately, and the symbol Redirects only affect Output.

Returning to the main topic, we recall that the .NET object model inside powershell fully exists within the framework of one computer (single OS), when remote code is run through WSMAN, objects are transferred via XML serialization, which brings a lot of additional interest to our research. Let's continue the experiments by running the following code:

$res=Invoke-Command -ComputerName . -ScriptBlock { &'D:\sqlagent\TestOutput1.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
    "exitCode",  : "System.Object[]",  "SetShouldExit"   "System.Int32": "    "System.Object[]"  "System.Object[]"   "System
.Int32"."
D:\sqlagent\TestOutput3.ps1:3 :1
+ $host.SetShouldExit($res)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

ERRORLEVEL=0

Great result! It means that when Invoke-Command is called, the pipeline is divided into two streams (Host and Output), which gives us hope of success. Let's try to leave only one value in the Output stream, for which we change 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)

Run it like this:

$res=Invoke-Command -ComputerName . -ScriptBlock { &'D:\sqlagent\TestOutput1.ps1' $args[0] } -ArgumentList $args[0]
$host.SetShouldExit($res)

and ... YES, it seems like a victory!

Out to host.
ExitCode: 4

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Int32                                    System.ValueType


ERRORLEVEL=4

Let's try to figure out what happened with us. We called locally powershell, which in turn called powershell on the remote computer and executed our script there. Two streams (Host and Output) from the remote machine were serialized and sent back, while the Output stream, if it had one digital value, was converted to Int32 type and transmitted to the receiving side in this form, and the receiving side used it as the calling exit code powershell.

And as the last check, we will create on the SQL server a one-step task with the type "Operating system (cmdexec)" with the following text:

PowerShell -NonInteractive -NoProfile "$res=Invoke-Command -ComputerName BACKUPSERVER -ConfigurationName SQLAgent -ScriptBlock {&'D:\sqlagent\TestOutput1.ps1' 6}; $host.SetShouldExit($res)"

HOORAY! The task failed, the text in the log:

   : DOMAIN\agentuser. Out to host. ExitCode: 6.     6.     .

Findings:

  • Avoid using Write-Output and specifying expressions without assignment. Remember that moving this code to another place in the script may lead to unexpected results.
  • In scripts intended not for manual launch, but for use in your automation mechanisms, especially for remote calls via WINRM, do manual error handling through Try / Catch, and ensure that this script sends exactly one to the Output stream for any development of events primitive type value. If you want to get the classic Errorlevel - this value must be numeric.

More articles:


All Articles