Unicode support for Invoke-Sqlcmd in PowerShell

Question

The PowerShell sqlps module provides core support for SQL Server access from within PowerShell and its Invoke-Sqlcmd cmdlet is its main workhorse for executing literal queries or SQL script files (analogous to the non-PowerShell sqlcmd utility). I recently tried some experiments to confirm that Invoke-Sqlcmd handles Unicode and had some surprising results.

I started with this simple script file (named unicode.sql):

CREATE TABLE #customers

( [IdCust] int,
  [FirstName] nvarchar(25),
  [SurName] nvarchar(25)
);
INSERT INTO #customers VALUES (4, N'Hans', N'Grüßner')
SELECT * FROM #customers;
DROP TABLE #customers;

Note that the surname has some typical Unicode characters one might find in a German name, for example.

---

Results

SQL Server Management Studio: Renders correctly when output to grid or to text, e.g.

IdCust      FirstName                 Surname
----------- ------------------------- -------------------------
4           Hans                      Grüßner

sqlcmd utility: Renders correctly whether run from a DOS shell or a PowerShell, e.g.

C:\> sqlcmd -S .\SQLEXPRESS -i unicode.sql

IdCust      FirstName                 Surname
----------- ------------------------- -------------------------
          4 Hans                      Grüßner

PowerShell Invoke-Sqlcmd: Renders incorrectly (whether output as text as shown below or piped into Out-Gridview):

PS> Invoke-Sqlcmd -Server .\sqlexpress -InputFile unicode.sql

IdCust FirstName           Surname
------ ---------           -------
     4 Hans                Gr??ner

The MSDN documentation for Invoke-Sqlcmd mentions Unicode only in passing, comparing its command-line switches with those of sqlcmd, showing that while the latter has a -u option for outputting Unicode (which was not even needed in my experiment above), Invoke-Sqlcmd has no equivalent parameter.

I have found nothing at all regarding this point through extensive web searching but I still hold out hope that this is in some way a user error on my part. Is there a way to preserve the input data when retrieving it with Invoke-Sqlcmd in PowerShell?

Refrigeration

I think you should change the character encoding of the SQL Server you are using. – Garter

What is your Powershell setting of $OutputEncoding? Interesting question, I look forward to having some time to test this. – Stewardess

$OutputEncoding was set to US-ASCII. I just tried setting it to [Text.Encoding]::Unicode and then to [Text.Encoding]::utf8 but no change to the result in either case. – Refrigeration

Answer 1

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

Fronia

Thanks for the info, Chad, but a couple questions. When I attempt to run your code I get "Incorrect syntax near the keyword 'VALUES'" -- even though the query runs fine by itself in SSMS. Any way around that error?... So I instead tried setting the query to just select N'Grüßner' as surname but this is apparently not a valid equivalent since it works even with Invoke-Sqlcmd. – Refrigeration

With here-strings aka verbatim strings you need to run them separately copy and past the $query through "@ by itself. Also it's easier to use Powershell_ise then you don't have to worry about. As you said probably not a valid test anyways. – Fronia

See update2. When Use the -Query parameter you code works fine with -inputfile it does not. – Fronia

Since you isolated the issue to -InputFile I separated input from output. First I created a non-temp table and inserted the unicode name. Then select * from uni_customers correctly retrieves it whether using -Query or -InputFile, so output works fine. I emptied the table for the input test, then did two separate inserts. A select * now returned two rows, one good (inserted with -Query) and one bad (inserted with -InputFile). Conclusion: data retrieved from a DB with PowerShell is valid; it is only when inserting and only from a file that has a problem. – Refrigeration

Good catch. Based on your observation I've narrowed it down even further. The inputfile will work if the .sql file is encoding with UTF-8 or UTF-32. I've posted some code and explanation. – Fronia

Thanks for tying up all the loose ends on this, Chad! I've given you the nod on your answer as well as giving you credit in a post at the end of my article Practical PowerShell for SQL Server Developers and DBAs – Part 1. – Refrigeration

Really helpful post. Top of my Google search results. What could of been a deep rabbit hole for me was solved with your help in 10 minutes. Kudos. – Choppy

Answer 2

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'

Harbaugh