Unicode support for Invoke-Sqlcmd in PowerShell

Update I tested invoke-sqlcmd on another machine and it works, so maybe the rest of this doesn't apply...

Update 2 Only seems to have issue with -inputfile when executing via -Query parameter invoke-sqlcmd works fine.

From what I can tell this has something to do with ADO.NET DataTable when converting a string. It works fine when you use an ExecuteScaler or ExecuteReader. Of course this doesn't fix invoke-sqlcmd, but does explain why:

$server = "$env:computername\sql1"
$database = "tempdb"
$query = @"
CREATE TABLE #customers

(     [SurName] nvarchar(25)
);
INSERT INTO #customers VALUES (N'Grüßner')
SELECT * FROM #customers;
"@


$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$connection.Open()
$command.ExecuteScalar()
$connection.Close()

Update 3 The encoding of the file seems to be the key. Looking at [System.IO.File]::ReadAllText, the MSDN doc states it will only detect UTF-8 or UTF-32 encoding. http://msdn.microsoft.com/en-us/library/ms143369(v=vs.90).aspx

If I save the .sql file with UTF-8, using the -inputfile param works. You can choose UTF-8 when saving .sql file in SSMS, but here's some Powershell code to check and change the encoding also. You'll need to grab Get-FileEncoding.ps1 from http://poshcode.org/2075

. .\Get-FileEncoding.ps1 
Get-FileEncoding -Path E:\bin\unicode.sql

$query = get-content E:\bin\unicode.sql
$query= $query -join "`n"
$query | Out-File -FilePath e:\bin\unicode.sql -Encoding UTF8 -force

Get-FileEncoding -Path E:\bin\unicode.sql