We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ).

The same script works fine, if I just execute it through SQL Server Management Studio, but through Powershell it's not rendering these special characters properly.

Here how we access the script file to execute on all databases on instance

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
invoke-Sqlcmd -InputFile "C:\DailyPatch\run.sql" | Out-File -filePath "C:\DailyPatch\dbname.txt" 
$text=Get-Content C:\DailyPatch\dbname.txt
$a = $text.length
$a=$a-3

$logFile = 'C:\DailyPatch\dblog.txt'

for ($i=3 ; $i -le $a ;$i++)
{   
    write-host $text[$i]
    $error.clear()

    Invoke-sqlcmd -inputfile "C:\DailyPatch\Script4AllDBs.sql"-database $text[$i]-OutputSqlErrors $true|Out-File -filePath "C:\DailyPatch\dbname.txt"

    foreach ($rr in $error) {
        $text[$i] >> $logFile
        $rr >> $logFile
    }
}

How to set collation in Powershell to avoid such kind of issues?

ps. Unicode thing is already handled on script side

Added: Special characters are being changed to � (Replacement Character)

  • What do you mean it is not "rendering properly"? – Steve Mangiameli Mar 15 '16 at 13:21
  • At what point do you get the problematic output? What does it look like? I created a table with a nvarchar field and copy pasted your ô in there then exported using powershell to txt and I don't see any problems. – Tom V Mar 15 '16 at 14:30

This issue has nothing to do with SQL Server Collations. The problem happens (i.e. the characters get transformed) prior to connecting to SQL Server. If the problem could be resolved by prefixing the string with an upper-case "N", then the script wouldn't work correctly in SSMS (as the OP states it does).

The problem here has to do with the encoding of the file not being known to Invoke-Sqlcmd, thus the bytes making up the file are interpreted incorrectly. This is why it does work correctly when pasting into SSMS (since the encoding of that window is UTF-16LE, like all of .NET / Windows, and is known to SSMS).

I checked the documentation and it does not appear that there is a parameter to set the file encoding like there is with SQLCMD. I assume that the file is being saved as either UTF-16LE ("Unicode" in Microsoft-land) or as UTF-8, but in either case, without a byte-order mark (BOM). I suggest re-saving the file with an encoding that includes the Byte Order Mark ("save with encoding" in some programs like SSMS). Be sure to pick the encoding that specifies either "BOM" or "with signature" (like in MS products). The BOM is 2 - 4 characters (depending on the encoding being used) at the beginning of the file that indicates the encoding being used (i.e. UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE). The BOM is a Unicode-only feature and does not exist for non-Unicode encodings. With it being set in the file, any program that opens the file will detect it and know what the encoding is without you needing to specify. Hopefully this holds true for Invoke-Sqlcmd.

If you are using strings in your SQL script (C:\DailyPatch\run.sql) then I would just ensure you put a N before the single quoted strings.

Use this:

select * from table where column = N'value ô'

Instead of:

select * from table where column = 'value ô'

The Unicode value should then be preserved if the column is nvarchar or nchar.

You can find the server collation with the following powershell:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "."

$srv.information

Replace the "." with you instance name.

If you don't specify the database (-database) in Powershell the default SQL server collation is used.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.