Microsoft – SQL Server / PowerShell – Invoke-SQLCmd Cmdlet – Error – Incorrect Syntax

Microsoft – SQL Server / PowerShell – Invoke-SQLCmd Cmdlet – Error – Incorrect Syntax

Had a little nice PowerShell Script on my hands.  Able to read SQL Scripts and execute them.  But, lately has been failing with a bogus error

The error read:

Incorrect Syntax near ‘)’

I knew it had to do with the fact that you never quite sure what you end up with when you read a text file and attempt to use it as a payload.  Lines do not always line up.

So launched Notepad++ and made sure that tabs were spaces, etc.

But, still no love.

Using Powershell, Looked for ways of preserving special characters  (line breaks, tabs, spaces, etc. But, nothing magical outside of XML white spaces.

Also, just to be sure launched SQL Profiler and captured SQL and corresponding error messages.

Error Message:

     Error: 120, Severity: 15, State: 1

So got focused a bit.

And, converted from:

$myServer = "LAB_DB";
$sqlFile = "d:\sammie.txt";

##read text file into an object 
$objQuery = get-Context $sqlFile;

##convert object into a String Buffer
$query = $objQuery -join "  "

## execute command in String Buffer
$objRS = Invoke-SqlCmd -Query $query -ServerInstance $myServer 
          -QueryTimeout 2000 -AbortOnError

to

$myServer = "LAB_DB";

$sqlFile = "d:\sammie.txt"; 

$objRS = Invoke-SqlCmd -InputFile $sqlFile 
            -ServerInstance $myServer 
            -QueryTimeout 2000 
            -AbortOnError

 

As I prepared this little post, I found it was my “BAD” after all:

(I should have concatenated the lines using Powershell new line)

$myServer = "LAB_DB";
$sqlFile = "d:\sammie.txt";

$objQuery = get-Context $sqlFile;

## separate using newline
$query = $objQuery -join " `n "

## 
$objRS = Invoke-SqlCmd -Query $query 
            -ServerInstance $myServer -QueryTimeout 2000
            -AbortOnError

Good Relevant Quotes:

References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s