Databases
StackOverflow2013
Postsdbo
POUnicode support for Invoke-Sqlcmd in PowerShell
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnicode support for Invoke-Sqlcmd in PowerShell
    primarykey
    data
    text
    <p>The PowerShell <a href="http://msdn.microsoft.com/en-us/library/hh245198">sqlps module</a> provides core support for SQL Server access from within PowerShell and its <a href="http://msdn.microsoft.com/en-us/library/cc281720">Invoke-Sqlcmd</a> cmdlet is its main workhorse for executing literal queries or SQL script files (analogous to the non-PowerShell <a href="http://msdn.microsoft.com/en-us/library/ms162773.aspx">sqlcmd</a> utility). I recently tried some experiments to confirm that Invoke-Sqlcmd handles Unicode and had some surprising results.</p> <p>I started with this simple script file (named unicode.sql):</p> <pre><code>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; </code></pre> <p>Note that the surname has some typical Unicode characters one might find in a German name, for example. </p> <hr> <h2>Results</h2> <p><strong>SQL Server Management Studio:</strong> Renders correctly when output to grid or to text, e.g.</p> <pre><code>IdCust FirstName Surname ----------- ------------------------- ------------------------- 4 Hans Grüßner </code></pre> <p><strong>sqlcmd utility:</strong> Renders correctly whether run from a DOS shell or a PowerShell, e.g.</p> <pre><code>C:\&gt; sqlcmd -S .\SQLEXPRESS -i unicode.sql IdCust FirstName Surname ----------- ------------------------- ------------------------- 4 Hans Grüßner </code></pre> <p><strong>PowerShell Invoke-Sqlcmd:</strong> Renders incorrectly (whether output as text as shown below or piped into Out-Gridview):</p> <pre><code>PS&gt; Invoke-Sqlcmd -Server .\sqlexpress -InputFile unicode.sql IdCust FirstName Surname ------ --------- ------- 4 Hans Gr??ner </code></pre> <p>The <a href="http://msdn.microsoft.com/en-us/library/cc281720">MSDN documentation</a> for Invoke-Sqlcmd mentions Unicode only in passing, comparing its command-line switches with those of sqlcmd, showing that while the latter has a <code>-u</code> option for outputting Unicode (which was not even needed in my experiment above), Invoke-Sqlcmd has <em>no</em> equivalent parameter. </p> <p>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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMichael Sorens
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUnicode support for Invoke-Sqlcmd in PowerShell
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think you should change the character encoding of the SQL Server you are using.
      singulars
      1. POUnicode support for Invoke-Sqlcmd in PowerShell
      1. USLajos Arpad
    2. COWhat is your Powershell setting of $OutputEncoding? Interesting question, I look forward to having some time to test this.
      singulars
      1. POUnicode support for Invoke-Sqlcmd in PowerShell
      1. USBruce
    3. CO$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.
      singulars
      1. POUnicode support for Invoke-Sqlcmd in PowerShell
      1. USMichael Sorens
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload