Sql-server – TSQL Script Execution Through Power shell, Special Characters Rendering Problem

encodingpowershellsql servert-sqlunicode

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)

Best Answer

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.

Related Question