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
– Abraham Zinala, Commented Oct 31, 2021 at 0:27
Did you mean: $argument = "greeting=" + $hello, "audience=world"; Invoke-Sqlcmd -Query "select '$argumemt'"? — Abraham Zinala
– Abraham Zinala, Commented Oct 31, 2021 at 0:29
Everything in the post is correct; the second version works just fine. Check the Invoke-Sqlcmd docs; I'm doing the exact thing as example 3. — Joel Christophel
– Joel Christophel, Commented Oct 31, 2021 at 0:38
Don't use $args as variable name, as Abraham pointed out, it's an automatic PS variable. See $args — Santiago Squarzon
– Santiago Squarzon, Commented Oct 31, 2021 at 0:43
Ah, gotcha. Good idea, though same thing with a different variable name. I'll update the question to avoid confusion. — Joel Christophel
– Joel Christophel, Commented Oct 31, 2021 at 0:51

Santiago Squarzon · Accepted Answer · 2021-10-31 01:22:10Z

4

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

edited Oct 31, 2021 at 1:22

answered Oct 31, 2021 at 1:09

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Christophel Over a year ago

I could have sworn the types were the same when I checked. Oh, well. Thanks for setting me straight!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related