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

powershellsql server

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.

Best Answer

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

Related Topic