Invoke-Sqlcmd : Incorrect syntax near '¦'. Encoding issue

Question

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"
}

That means the argument you've provided to the -Query parameter is not valid T-SQL. Please post the query text. — Mathias R. Jessen
– Mathias R. Jessen, Commented Jan 14, 2022 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
– Ledda, Commented Jan 14, 2022 at 14:35
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
– Sage Pourpre, Commented Jan 14, 2022 at 14:56
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
– mklement0, Commented Jan 14, 2022 at 15:03
Powershell 5 can't recognize utf8 no bom. It comes up often. — js2010
– js2010, Commented Jan 14, 2022 at 18:28

js2010 · Accepted Answer · 2022-01-16 15:46:11Z

2

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:

'æøå'

edited Jan 16, 2022 at 15:46

answered Jan 15, 2022 at 16:11

js2010

28.4k6 gold badges82 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invoke-Sqlcmd : Incorrect syntax near '¦'. Encoding issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related