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?