powershellinvoke-sqlcmd

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


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.


Solution

  • 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