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.

Improve this question
5
  • Use semicolon to separate SQL commands. You either need a return between commands or a semicolon (not comma). Commented Apr 10, 2023 at 8:52
  • If your Sql module is V22+ you can try and use a Hashtable instead of an array: $var = @{MYVAR1='String1'; MYVAR2='String2=String3'} Commented Apr 10, 2023 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… stackoverflow.com/questions/35157090/…. It’s hacky, but looks like a limitation of Invoke-SqlCmd Commented Apr 10, 2023 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)+'"))'" Commented Apr 10, 2023 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. Commented Apr 10, 2023 at 12:39
Related questions
4
Error passing non-literal scripting variables to Invoke-Sqlcmd in PowerShell
1
invoke-sqlcmd empty variable
14
Escape variable in sqlcmd / Invoke-SqlCmd
Load 4 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.