0

In a PowerShell script, I read a config file and run some SQL queries. This works fine if I copy the code and paste it into PowerShell ISE.

But if I run the script by right clicking the file > Run with Powershell, I get an error

Invoke-Sqlcmd : Incorrect syntax near '¦'

I only get this error if the query contains scandinavian characters 'æøå'.

$config = Get-Content -Path <path to file>\config.json | ConvertFrom-Json

$server = $config.server
$database = $config.database

Invoke-Sqlcmd -ServerInstance $server -Database $database -QueryTimeout 0 -Query "select Næringskode from Virksomhet"

config.json

{
    "server": "localhost",
    "database": "MyDatabase"
}
Ledda
  • 35
  • 6
  • 1
    That means the argument you've provided to the `-Query` parameter is not valid T-SQL. Please post the query text. – Mathias R. Jessen Jan 14 '22 at 14:15
  • Unfortunately, I can't post the query in its entirety because of sensitive information. But upon further testing I found that the issue has to do with using scandinavian characters 'æøå'. Table and column names might consist of these letters. So a simple `select Næringskode from Virksomhet` will throw this exception. – Ledda Jan 14 '22 at 14:35
  • That's a great example - please update the code in your post with such a query and the (exact) error SQL produces for that specific query – Mathias R. Jessen Jan 14 '22 at 14:41
  • Is the error message still _exactly_ `Incorrect syntax near '¦'`? (or is it `near `?) – Mathias R. Jessen Jan 14 '22 at 14:45
  • Ditch the double quotes. Use single quotes around your query. It might be the source of your issues. Double quotes string get expanded. It might solve your issues. I had issues with SQL in the past and double quotes with some queries. – Sage Pourpre Jan 14 '22 at 14:49
  • It is exactly `Incorrect syntax near '¦'`, even though this character is not in the query. – Ledda Jan 14 '22 at 14:50
  • I tried using single-quotes, but it didn't affect the result for me. When selecting a column without 'æøå' it works fine. Both with single and double quotes. – Ledda Jan 14 '22 at 14:54
  • 1
    Does it work when debugging through F8 and fail when executing the script ? If it work when debugging but not when running the file, it might be the file encoding which is not Unicode. – Sage Pourpre Jan 14 '22 at 14:56
  • 1
    Excellent pointer, @SagePourpre. Ledda, try saving your PowerShell script file as UTF-8 _with BOM_ - Windows PowerShell needs that in order to recognize UTF-8 files. – mklement0 Jan 14 '22 at 15:03
  • @Ledda, if saving as UTF-8 _with BOM_ solves the problem, we can close your question as a duplicate of [this one](https://stackoverflow.com/q/54789907/45375). – mklement0 Jan 14 '22 at 15:24
  • 1
    Powershell 5 can't recognize utf8 no bom. It comes up often. – js2010 Jan 14 '22 at 18:28

1 Answers1

1

Make sure the script is saved in an encoding powershell 5.1 can recognize (I'm looking at Notepad in Win10 20h2) (ansi is probably the worst choice):

ansi
utf-16 le
utf-16 be
utf-8 with bom

not (unless you're in powershell 7).

utf-8

Notepad can recognize utf-8.

test.ps1:

'æøå'
js2010
  • 23,033
  • 6
  • 64
  • 66