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

Question 1

Setup:

CREATE TABLE MyTest (TestCol1 nchar(5))

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

1

2

3

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 ('`
<h1>Question:</h1>




<div class="s-prose js-post-body" itemprop="text"><code>

Setup: 

<pre><code>CREATE TABLE MyTest (TestCol1 nchar(5))

1

2

3

4

5

6

7

8

9

10

11

12

13

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 ('`

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

1

2

3

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:

(5′)”

Question 2

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.