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