1

I took an example from the document to add the variable of Invoke-Sqlcmd

$StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Var1     Var2
----     ----
String1  String2

But the value of the variable cannot have an equal sign, here is an example

$StringArray = "MYVAR1='String1'", "MYVAR2='String2=String3'"
Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

I got error: 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.

Is there any way to bypass that.

Tan
  • 149
  • 1
  • 9
  • Use semicolon to separate SQL commands. You either need a return between commands or a semicolon (not comma). – jdweng Apr 10 '23 at 08:52
  • If your Sql module is V22+ you can try and use a Hashtable instead of an array: `$var = @{MYVAR1='String1'; MYVAR2='String2=String3'}` – Theo Apr 10 '23 at 10:46
  • 1
    There’s a similar / duplicate question here that has some kludgy workarounds like using “replace” in the sql query to substitute a known placeholder in the variable value for an equals sign… https://stackoverflow.com/questions/35157090/escape-variable-in-sqlcmd-invoke-sqlcmd. It’s hacky, but looks like a limitation of ```Invoke-SqlCmd``` – mclayton Apr 10 '23 at 10:48
  • 1
    Another option I found could be to replace the `=` in the variable value by `CHAR(61)` like this: `$StringArray = "MYVAR1='String1'", "MYVAR2='$('String2=String3'.Replace('=', "'+CHAR(61)+'"))'"` – Theo Apr 10 '23 at 10:57
  • 2
    @jdweng, there's only _one_ query in the question, so your comment doesn't apply and is an unnecessary distraction. Please consider deleting it. – mklement0 Apr 10 '23 at 12:39

0 Answers0