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 3The 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 -forceGet-FileEncoding -Path E:\bin\unicode.sql

When executing SQLCMD, you must specify the encoding.

EXEC xp_cmdshell 'for %f in ("{Dir}*.sql") do sqlcmd -S {Server} -U {username} -P {password} -d {database} -i "%f" -b -f 65001'