How to pass variable value from a map defined outside invoke command and to be used after invoke command

Question

I am writing a script in powershell where after login with User 1 on a system, it will switch to user 2 and then make a connection to database with this user. However, the dbinstance details, port No and Computer name to be passed in invoke command will be defined as a map before the 2nd invoke command i.e. when it will invoke the command to open powershell with 2nd user(db user). It is able to take userid in this case i.e. when to invoke the powershell connection with 2nd user, however it is not able to pass the values of dbinstance and port to next sqlcmd invoke. Below is the code for reference. In this code it works fine while getting $inputMap.UserNameP, however it fails in passing $inputMap.DBInstance,$inputMap.PortNo.

$UserName =  'User1' 
$securekey = @'
securekey1
'@ |ConvertTo-SecureString -AsPlainText -Force;
$concreds=New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $securekey;
Invoke-Command -Credential $concreds -ComputerName 'abc.domainname'-Authentication Credssp -ScriptBlock {
function checkFaultHighUtilization() {
$local:ExecStdOperatorOut=Invoke-Command -ScriptBlock {
$inputMap=@{"UserNameP"="User2";"DBInstance"="databaseinstancename";"PortNo"="portnumber";};
$securekey1 = "securekey1"
$finalresult = @()
$securekey2 = $securekey1 | ConvertTo-SecureString -AsPlainText -Force;
$concreds=New-Object System.Management.Automation.PSCredential -ArgumentList $inputMap.UserNameP, $securekey2;
Invoke-Command -Credential $concreds -ComputerName 'computername' -Authentication Credssp  -ScriptBlock {
$var1=Invoke-Sqlcmd  -query "
Begin
select  * from db
End" -ServerInstance "$inputMap.DBInstance,$inputMap.PortNo"
##if (($var1.count) -gt 0) {
foreach($row in $var1){
$finalresult+=$row.a+':'+$row.b+':'+$row.c
echo $finalresult
}
}
}
$local:ExecStdOperatorRet=if($local:ExecStdOperatorOut) {0} else {1}
return $local:ExecStdOperatorRet,$local:ExecStdOperatorOut;
};
$ESExecReturn,$ESExecOutput=checkFaultHighUtilization
$ESExecOutput=($ESExecOutput | Out-String).Trim();
Write-output "ESExecOutput:";
Write-output $ESExecOutput;
Write-output ":ESExecOutput";Write-output $("ESExecError:" + $Error + ":ESExecError");
Write-output $("ESExecReturn:" + $ESExecReturn + ":ESExecReturn");
}

[Use the Param keyword in a script block](https://learn.microsoft.com/en-gb/powershell/module/Microsoft.PowerShell.Core/Invoke-Command?view=powershell-7.1#example-11--use-the-param-keyword-in-a-script-block). — JosefZ, Dec 20 '20 at 14:30
From what I can tell, the problem really only was the syntactically broken string interpolation, but the amount of code without indentation made that hard to spot. Similarly, it's not obvious how the accepted answer solves your problem. As an aside: to invoke a script block locally, you can simply use `& { ... }`. — mklement0, Dec 23 '20 at 14:33

score 0 · Accepted Answer · answered Dec 20 '20 at 13:55

$scriptBlockOne = {
    $variableA = "Hello World"
    return $variableA
}

$scriptBlockTwo = {
    param (
        $inputString
    )
    Write-host $inputString
}

$invokeCommandReturn = Invoke-Command -ScriptBlock $scriptBlockOne
Invoke-Command -ScriptBlock $scriptBlockTwo -ArgumentList $invokeCommandReturn

mklement0 · Answer 2 · 2020-12-23T14:28:17.893

You're trying to use expressions such as $inputMap.DBInstance as-is inside an expandable string ("..."), which is syntactically not supported.

To use expressions, you must enclose them in $(...), the subexpression operator.
See this answer for a comprehensive discussion of string interpolation in PowerShell.

Therefore:

# ...

$var1 = Invoke-Sqlcmd -Query "
Begin
select  * from db
End" -ServerInstance "$($inputMap.DBInstance),$($inputMap.PortNo)" # Note the $()

# ...

How to pass variable value from a map defined outside invoke command and to be used after invoke command

2 Answers2