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: 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
$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:
- An unexamined life is not worth living ( Socrates – http://en.wikiquote.org/wiki/Socrates)
- To Teach is to Learn Twice (http://www.brainyquote.com/quotes/authors/j/joseph_joubert.html)
- How many people make themselves abstract to appear profound. The most useful part of abstract terms are the shadows they create to hide a vacuum.
- I hope you do not deny others the liberty of learning though your daily struggles (Daniel Adeniji)
- Invoke SQLCmd Cmdlet (http://technet.microsoft.com/en-us/library/cc281720.aspx)