none
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. RRS feed

  • Question

  • Hi,

    I am working with a SQL script that's being called from PS

    $something = "1111"

    Invoke-Sqlcmd -ServerInstance $sqlinst -Database $dbname -Username 'dbuseracccoutn' -Password '********' -Variable "tempKEY=$something" -InputFile $inpfile -Verbose 4>&1 | Tee-Object -Variable statistics 

    When I run the command above, I am able to pick up $something variable in my SQL 


    If I change $something = "abc" I get Invoke-Sqlcmd : The name "abc" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    If I change $something = "http://www.securesite.com?a=3&b=f" I get  Invoke-Sqlcmd : 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.

    Could anyone please point one what I am missing with the format or / and syntax ?

    Thanks 

    Wednesday, October 2, 2019 12:54 PM

All replies

  • You need to use correct SQL syntax in the variable string.

    -Variable "tempKEY='$something'"


    \_(ツ)_/


    • Edited by jrv Wednesday, October 2, 2019 3:42 PM
    Wednesday, October 2, 2019 3:42 PM
  • correct syntax gets me the same  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 error
    Wednesday, October 2, 2019 5:11 PM
  • This can happen for many reasons - mostly the variable defined in the SQL is defined wrong.

    You will need to post this and your SQL in the T-SQL or SQLServer forum to get help fixing the SQL.

    A suggestion is to write a simple SQL select that uses a variable and test it until you understand how this works.

    Here is a sample to test with.  It will work on any sql database.

    $var = 'status'
    Invoke-Sqlcmd -Query 'select id from syscolumns where name=$(name)' -ServerInstance .\sqlexpress -variable "name='$var'"

    Note that the single quotes can be placed in the SQL. In that case don't use them in the variable declaration. Do not quote numeric field values.


    \_(ツ)_/


    • Edited by jrv Wednesday, October 2, 2019 5:37 PM
    Wednesday, October 2, 2019 5:34 PM
  • your example works

    here is the value that gets assigned to my $something variable. Is there anything in the value that would break the syntax if not escaped ?

    https://junkfiles.blob.core.windows.net/some-folder?sv=2019-02-02&sr=c&sig=HOtd1AorRfYtimAJY5PeVJikKiTYgcs81QeASqpXPec%3D&se=2019-11-02T17%3A42%3A01Z&sp=rw


    • Edited by net_tech Wednesday, October 2, 2019 5:55 PM
    Wednesday, October 2, 2019 5:46 PM
  • I see no SQL here.  Did you try my example?


    \_(ツ)_/

    Wednesday, October 2, 2019 5:56 PM
  • yes, as I posted previously your example works. 

    here is the actual script

    $something = 'https://junkfiles.blob.core.windows.net/some-folder?sv=2019-02-02&sr=c&sig=HOtd1AorRfYtimAJY5PeVJikKiTYgcs81QeASqpXPec%3D&se=2019-11-02T17%3A42%3A01Z&sp=rw'

    Invoke-Sqlcmd -ServerInstance $sqlinst -Database $dbname -Username 'dbuseracccoutn' -Password '********' -Variable "tempKEY=$something" -InputFile $inpfile -Verbose 4>&1 | Tee-Object -Variable statistics

    Wednesday, October 2, 2019 6:33 PM
  • Without the SQL and schema for the column there is no way to answer your question.


    \_(ツ)_/

    Wednesday, October 2, 2019 6:41 PM
  • here is the SQL 

    select * from Translation where nTranslationID=777
    print $(tempKEY)
    here is the PS
    $var= 'https://junkfiles.blob.core.windows.net/some-folder?sv=2019-02-02&sr=c&sig=HOtd1AorRfYtimAJY5PeVJikKiTYgcs81QeASqpXPec%3D&se=2019-11-02T17%3A42%3A01Z&sp=rw'
    
    
    invoke-Sqlcmd -ServerInstance 'db1' -Database 'whatsup' -Username 'zzzzz' -Password '******' -Variable "tempKEY='$var'" -InputFile "c:\temp\zzzz.sql" -Verbose 4>&1 | Tee-Object -Variable statistics 

    it's the = in the variable value that's causing the error

    Thursday, October 3, 2019 1:46 AM
  • The variable contents apparently has illegal characters.

    Post this issue in the T-SQL forum for further help.


    \_(ツ)_/

    Thursday, October 3, 2019 1:54 AM
  • this thread has a few options that could be used 

    https://stackoverflow.com/questions/35157090/escape-variable-in-sqlcmd-invoke-sqlcmd

    I ended up re-purposing this script in PS 

    $equalsSignReplacement = '[EQUALSIGN]'
    $myVariable = 'aGVsbG8NCg=='
    $variables = 
        "EqualsSignReplacement=$($equalsSignReplacement)",
        "MyVariable=$($myVariable.Replace('=',$equalsSignReplacement))"
    
    Invoke-SqlCmd `
        -ServerInstance 'localhost' `
        -Database 'master' `
        -Username 'matthew' `
        -Password 'qwerty' `
        -Query 'SELECT REPLACE('$(MyVariable)','$(EqualsSignReplacement)','=') AS foo' `
        -Variable $variables

    and this on the SQL side

    declare @str1 as varchar(max);
    declare @str2 as varchar(max);
    
    
    set @str1 = $variables
    set @str2 = REPLACE(@str1, '[EQUALSIGN]', '=')
    
    print @str2
    


    • Edited by net_tech Saturday, October 5, 2019 8:54 PM
    Thursday, October 3, 2019 2:47 AM