Error when inserting strings with $( with Invoke-SqlCmd in Powershell

Question

Setup:

CREATE TABLE MyTest (TestCol1 nchar(5))

Test:

Following work:

Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -OutputSqlErrors $True -Query "INSERT INTO MyTest VALUES ('`$5')"
Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -OutputSqlErrors $True -Query "INSERT INTO MyTest VALUES ('(5')"

Following fails with the error below:

Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -OutputSqlErrors $True -Query "INSERT INTO MyTest VALUES ('`$(5')"
Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -OutputSqlErrors $True -Query "INSERT INTO MyTest VALUES ('`$`(5')"

Error: Invoke-Sqlcmd : At line:1 char:1 + Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -Ou ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Aftersome research I found that $( provides functionality in powershell thus is reserved. However I tried escaping the parenthesis but with no success. I've tried finding alternatative. Any ideas on how I can do this? If I use the CHAR function in SQL Server that works but it would be a pain to deal with in my code. Thank you.

grasshope · Accepted Answer · 2014-12-02 15:59:44Z

6

All you need to do is disable the variables lookup in the invoke-Sqlcmd by adding -DisableVariables. $() is use to pass in variable into the sql statements.

Invoke-Sqlcmd -InputFile $fileName -ServerInstance $serverInstance -DisableVariables

Source http://msdn.microsoft.com/en-us/library/cc281720.aspx

answered Dec 2, 2014 at 15:59

grasshope

761 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CB. · Accepted Answer · 2013-03-22 21:27:43Z

1

try this:

'INSERT INTO MyTest VALUES ("$(5")'

or this:

'INSERT INTO MyTest VALUES (''$(5'')'

answered Mar 22, 2013 at 21:27

CB.

61.3k9 gold badges170 silver badges165 bronze badges

9 Comments

Bacon Bits Over a year ago

To provide a reason why this works: In PowerShell, double-quoted strings ("hello") are parsed for variables. Single-quoted strings ('hello') are treated as literal strings and are never parsed. Try the following: $x = 0; "$x"; '$x'. That said, your first example "INSERT INTO MyTest VALUES ('`$(5')" should be working. That is a correct string.

CB. Over a year ago

@BaconBits I think this comment was better in the OP, not in my answer. Or is for me this comment? :)

Russ960 Over a year ago

If I run it this way it is expecting more characters with the >> prompt.

Bacon Bits Over a year ago

@C.B. No, not really for you. It just isn't obvious why a single quote would work but a double wouldn't. I was expanding on your answer. The verbiage is a bit weird though. It should say "That said, the first example... ". :)

Bacon Bits Over a year ago

@Russ960 What are your database and server names? Do they have any reserved Powershell characters in them?

|

elpezganzo · Accepted Answer · 2014-03-14 19:04:57Z

0

The $(anyvalue) are reserved variables to be used with SQLCMD.EXE Change the symbol $ by the sql server CHAR(36):

Invoke-Sqlcmd -Database "databasename" -ServerInstance "hostname" -OutputSqlErrors $True -Query "INSERT INTO MyTest VALUES (CHAR(36)+'(5)')"

answered Mar 14, 2014 at 19:04

elpezganzo

3874 silver badges8 bronze badges

1 Comment

Russ960 Over a year ago

That is what I determined and posted here previously.

Russ960 · Accepted Answer · 2013-07-01 03:26:47Z

-1

The issue seems that the $( is a reserved word in Powershell thus to make this work I had use the CHAR function to store the $ (CHAR(36)). Once I did this it worked but for this project I abandoned using the Invoke-SqlCmd command and used a standard ADO.NET method. It seems that the Invoke-SqlCmd wants to parse the command and for the reserved combination continues to see the reserved word even if you escape the characters.

answered Jul 1, 2013 at 3:26

Russ960

1,3592 gold badges22 silver badges36 bronze badges

Comments

E.V.I.L. · Accepted Answer · 2013-03-22 22:57:00Z

-2

"INSERT INTO MyTest VALUES ('`$5')"

Outputs a string

INSERT INTO MyTest VALUES ('$5')

"INSERT INTO MyTest VALUES ('(5')"

Outputs a string

INSERT INTO MyTest VALUES ('(5')

"INSERT INTO MyTest VALUES ('`$(5')"

Outputs a string

INSERT INTO MyTest VALUES ('$(5')

"INSERT INTO MyTest VALUES ('`$`(5')"

Outputs a string

INSERT INTO MyTest VALUES ('$(5')

What do you want to insert? Actually '$5' or the value of the variable $5? Also, you have strange parentheses around your variable $5. Not sure if is suppose to be like that or your don't understand another important part of powershell variables? Because this a variable in a string "There are $((Get-Process).Count) processes running" too.

edited Mar 22, 2013 at 22:57

answered Mar 22, 2013 at 22:42

E.V.I.L.

2,16613 silver badges14 bronze badges

2 Comments

Russ960 Over a year ago

I am trying to insert $(5. Don't ask me why I'm moving data from one system to another.

E.V.I.L. Over a year ago

'INSERT INTO MyTest VALUES ("$(5")' or "INSERT INTO MyTest VALUES (<backtick>"<backtick>$(5)<backtick>")". Hope I helped and not confused.

Collectives™ on Stack Overflow

Error when inserting strings with $( with Invoke-SqlCmd in Powershell

5 Answers 5

Comments

9 Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

9 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related