Error passing non-literal scripting variables to Invoke-Sqlcmd in PowerShell

Question

I am trying to pass in some scripting variables to Invoke-Sqlcmd in PowerShell like so:

$hello = "hello"
$params = "greeting=" + $hello, "audience=world"
Invoke-Sqlcmd -Query "select '`$(greeting) `$(audience)'" -Variable $params

I get the following error:

The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the 'var=value' format for defining a new variable.

But I am successful if I remove $hello and use a literal:

$params = "greeting=hello", "audience=world"

.GetType() returns the same thing for both versions of $params, so I'm unsure what the issue is.

Don't assign to `$args` as it's an *automatic variable*. I've never used that cmdlet before but, I'm pretty sure you're not supposed to use `-Variable` like that. Also, what's your intentions, 'cause there is no actual variable in your string, so there is no need to use a *sub-expression*, nor to escape it using the *back tick*. — Abraham Zinala, Oct 31 '21 at 00:27
Did you mean: `$argument = "greeting=" + $hello, "audience=world"; Invoke-Sqlcmd -Query "select '$argumemt'"`? — Abraham Zinala, Oct 31 '21 at 00:29
Everything in the post is correct; the second version works just fine. Check the [Invoke-Sqlcmd docs](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps); I'm doing the exact thing as example 3. — Joel Christophel, Oct 31 '21 at 00:38
Don't use `$args` as variable name, as Abraham pointed out, it's an automatic PS variable. See [`$args`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_automatic_variables?view=powershell-7.1#args) — Santiago Squarzon, Oct 31 '21 at 00:43
Ah, gotcha. Good idea, though same thing with a different variable name. I'll update the question to avoid confusion. — Joel Christophel, Oct 31 '21 at 00:51

Santiago Squarzon · Accepted Answer · 2021-10-31T01:22:10.370

On your first example, the variable $params is being set to string:

$hello = "hello"
$params = "greeting=" + $hello, "audience=world"
$params.GetType()

IsPublic IsSerial Name          BaseType
-------- -------- ----          --------
True     True     String        System.Object

PS /> $params
greeting=hello audience=world

Unless you tell PowerShell you want an object[] as result of your operation. i.e.: surrounding the concatenation operation with ( ):

$params = ("greeting=" + $hello), "audience=world"
$params.GetType()

IsPublic IsSerial Name            BaseType
-------- -------- ----            --------
True     True     Object[]        System.Array

PS /> $params
greeting=hello
audience=world

Or using the array sub-expression operator for example:

$params = @(
    "greeting=" + $hello
    "audience=world"
)

For official documentation on this, see about_Operator_Precedence.

$string = 'a'
$array = 'b','c'

PS /> ($string + $array).GetType()

IsPublic IsSerial Name          BaseType
-------- -------- ----          --------
True     True     String        System.Object

PS /> $string + $array
ab c

PS /> ($array + $string).GetType()

IsPublic IsSerial Name            BaseType
-------- -------- ----            --------
True     True     Object[]        System.Array

PS /> $array + $string
b
c
a

I could have sworn the types were the same when I checked. Oh, well. Thanks for setting me straight! — Joel Christophel, Oct 31 '21 at 01:37

Error passing non-literal scripting variables to Invoke-Sqlcmd in PowerShell

1 Answers1