Recently I was working on SQL code and data migration engagement for a major Financial institiuation. I was leading the team of SQL developers on this proposition. We have been provided thousands of SQL scripts and hundreds of Stored Procedures along with similar amount of user-defined SQL database functions to convert them in new supporting format. Developers were converting the SQL codes, saving it and committing these to the version-control system GIT. During Database code analysis and review (which follows immediately after development) to reduce the number of “code smells” that creep into your database builds, the Governance team noticed something wrong with the file encoding.
So, What went wrong?
The file that is used to create a new query window has ANSI encoding but when they save the file it was in UTF-16. Unicode characters were broken 🙁
It didn’t took much time to figure out the issue and resolve, but I decided to write the article explaining steps to resolve.
The default encoding for SQL Server Management Studio sql files is UTF-16, more specifically, either Western European (Windows) - Codepage 1252 or Unicode - Codepage 1200. These encodings play havoc with a git diff as these encoding appear as binary files.
The preferred encoding is Unicode (UTF-8 with signature) - Codepage 65001
How to solve the File encoding in SSMS?
We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.
- From within SSMS, open the sql template file named
SQLFile.sql, by default in one of these locations: –%ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\%ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\%ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql%ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).
- Resave using correct encoding:
- File => Save As
- Click the arrow next to the Save button
- Choose the relevant encoding:
Unicode (UTF-8 with asignature) - Codepage 65001
All new query windows will default to UTF-8 files
5 replies on “How to change the default file encoding in Sql Server Management Studio”
What’s up to every one, the contents existing at this web site are
in fact remarkable for people knowledge, well, keep up
the good work fellows.
I will right away seize your rss feed as I can not
in finding your email subscription link or e-newsletter service.
Do you’ve any? Kindly permit me recognise in order that I may subscribe.
Thanks. Ahaa, its fastidious dialogue concerning this article here at this blog,
I have read all that, so now me also commenting at this place.
Way cool! Some extremely valid points! I appreciate
you penning this post and also the rest of the site is very
good.
It’s nearly impossible to find well-informed people about this topic, however, you seem like you know what you’re talking about!
Thanks
Admiring the time and effort you put into your
blog and in depth information you present. It’s awesome to come across a blog
every once in a while that isn’t the same unwanted rehashed
information. Great read! I’ve bookmarked your site and I’m including your RSS feeds to my Google account.
Does not work with Microsoft SQL Server Management Studio v 14.0.17277.0 or later.