Few days ago, some of our teammates discovered that the SQL files they tried to pull from our GitHub repo had encoding issue. When they did git pull, there would be an error saying “fatal: failed to encode ‘…/xxxxx.sql’ from UTF-16-LE-BOM to UTF-8”.
In addition, on GitHub, the SQL files we committed to the GitHub are all marked as binary files. Thus we couldn’t view the changes we made to those files in the commit.
Cause of the Issue
It turns out that those SQL files are generated from SQL Server Management Studio (SSMS).
By default, the encoding used to save SQL files in SSMS is UTF-16. For my case, my default encoding is the “Western European (Windows) – Codepage 1252”. Codepage 1252 is a single-byte character encoding of the Latin alphabet that was used in Windows for English and many Romance and Germanic languages. This encoding will cause Git to treat the files as binary files.
Solution
The way to resolve this issue is to force the file to use UTF-8 encoding. We can run the following PowerShell script to change the encoding of all SQL files in a given directory and its subdirectories.
$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
Get-ChildItem "<absolute directory path>" -Recurse *.sql | foreach {
$FilePath = $_.FullName
$FileContent = Get-Content $FilePath
[System.IO.File]::WriteAllLines($FilePath, $FileContent, $Utf8NoBomEncoding)
}
The BOM (Byte Order Mark), a sequence of bytes at the start of a text stream (0xEF, 0xBB, 0xBF), is used to signal the endianness of an encoding, but since endianness is irrelevant to UTF-8, the BOM is unnecessary. This explains why we pass $False to the constructor of UTF8Encoding to indicate that BOM is not needed.
Wrap-Up
That’s all for a short little PowerShell script we used to solve the encoding issue of our SQL files.
There is an interesting discussion on StackOverflow about this issue, please give it a read too.
KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.
cuteprogramming 