CODEWITHSUNDEEP
sql-serverpowershell
Russ960
Russ960

Reputation: 1339

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

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.

Upvotes: 5

Views: 8581

Answers (5)

grasshope
grasshope

Reputation: 76

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

Upvotes: 6

elpezganzo
elpezganzo

Reputation: 367

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)')"

Upvotes: 0

Russ960
Russ960

Reputation: 1339

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.

Upvotes: -1

E.V.I.L.
E.V.I.L.

Reputation: 2166

"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.

Upvotes: -2

CB.
CB.

Reputation: 60910

try this:

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

or this:

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

Upvotes: 1

Related Questions