Need a way to set the default encoding for query files in SMSS.
A script saved from SMSS into a .sql file is saved as UTF-16 (unicode) encoding by default. This is a similar issue to the one raised in another feedback entry where the output to CSV is saved as UTF-16. This behavior of SMSS is a departure from the way Query Analyzer (which I used for the last four years) handled these files and it has taken me quite some time to track down the reason things suddenly stopped working correctly.
Many version control systems (specifically CVS, SubVersion, SVN, and Perforce, which I have used) do not properly handle this encoding. This means that though the file looks fine on the original engineer's computer none of the other collaborators on the project can use the code.
The danger here is that the files appear fine on the original machine, but nowhere else. If anything happens to that machine then the code is lost. This problem completely undermines the purpose of source control systems and collaborative development.
I understand that it is possible to change the encoding at the time the file is saved. That option is not obvious and not well-documented. I have used SMSS for six months and I just found that option today. If the engineer forgets to explicitly chose this option (using that tiny arrow on the right had of the "Save" button that often looks like a speck on the monitor) and navigate through the resulting menu and drop list to the right encoding format then the result is still a file that version control cannot use, severely hampering the ability to do team collaboration.
The steps to save each file this way are tedious and easily forgotten when one is "In the Zone" of writing code on a project. In situations where this problem occurs then every file must be saved in this way.
Upvotes: 168<=-=Apr 8 2008 10:17AM=-=>
Chris – great feedback. I’m going to keep this suggestion on file (we can’t do this for SQL Server 2008, unfortunately) because I want to evaluate two of the areas you’ve mentioned, Source Control and Project Creation. We have quite a few things I want to do there to make the development experience better.
Thanks again for submitting.
Buck Woody, SQL Server Program Manager<=-=Jun 13 2008 9:00AM=-=>
We have encountered this same issue today. In our case, Visual Studio 2005 database project will create a new script file as ANSI.
Visual Studio 2008 creates new script files as UTF-8.
And SSMS creates them as Unicode 1200 (UCS-2 little endian) when doing Script CREATE to file (unless you do New Query, then File → Save, which saves as ANSI!)
So it’s a mess in the project and/or source control dealing with different file encodings when trying to work with the files.
The specific problem I had was simply creating a consolidated script to do an update to the database:
COPY script1.sql + script2.sql c:\scripts\consolidated.sql
You get an unusable script if the files are not all the same encoding.
So we need a way to have all the tools create and maintain script files in the same encoding.
Toby<=-=Jul 10 2008 8:53AM=-=>
Being unable to set the default file format is a big drawback to using SMSS. Is there no registry hack to get around this?<=-=Aug 28 2008 8:54AM=-=>
This has been a design flaw since SQL ’05 IMO. The “Save with Encoding…” routine is akward and the fact that your preference is neither preserved nor configurable in any menu just adds insult to injury.
Buck, why can this not be “fixed” / improved in SQL 2008? How about you meet us half-way…. update the save dialog code to read from a registry setting, then let those of us annoyed by this issue go tweak that setting…<=-=Dec 8 2008 1:30PM=-=>
I would like to amplify the sentiment of the other respondents here.
Along the lines of source control integration problems, recently a regression bug was introduced to current development due to SSMS’s failure to check out a file properly during a code fix. We use SSMS, and VSS 6.0d (ugh, I know). The file in question was a victim of this UTF-16 problem, and we believe that situation may have contributed to VSS’s mishandling of the file. Further it was harder to resolve the differences as we found out we couldn’t diff the unicode file within VSS as with ASCII files.
Does anyone know if SourceSafe 2005 behaves any better when integrated with SSMS?<=-=Jan 26 2009 6:05AM=-=>
This also causes problems in Rational Clearcase source control – its native version diffing functionality doesn’t support Unicode.<=-=Jan 26 2009 8:32AM=-=>
I have encountered this while generating multiple .csv file from SMSS. For each file I generate, i have to change the text box to “Ansi”.
Unicode is great, but useless in north american languages (french and english).<=-=May 6 2009 7:26AM=-=>
Does anyone know if an SSMS addin would be able to intercept the save operation and force a change to the codepage?<=-=May 6 2009 8:02AM=-=>
I don’t think an addin will help. i have searched the object model. The only event that comes close to helping is the DocumentEvents.DocumentSaved. That would be too late. Also, the Save and SaveAs methods don’t include the encoding as a parameter.
The best chance would be to hook the save dialog and for the encoding. I am guessing that is done with the options property. It is a bit mask and the bits are not defined.
Out of luck i guess.<=-=Sep 28 2009 2:54PM=-=>
We recently started using SQL 2008, and I noticed that my default encoding selection for saving script files is now Codepage 1252. The good news, for me, is that this is a much better default than Unicode for our development environment. I still don’t see a way to choose what you want the default to be.<=-=Jan 26 2010 2:23PM=-=>
Also would be nice to be able to set default encoding when saving results to a file (always selecting the little arrow and scrolling to ANSI is tedious)<=-=Jun 28 2010 8:39AM=-=>
I would like to agree with the sentimate posted by xyvyx on 8/28/2008 at 8:54 AM
["This has been a design flaw since SQL ’05 IMO. The “Save with Encoding…” routine is akward and the fact that your preference is neither preserved nor configurable in any menu just adds insult to injury.
Buck, why can this not be “fixed” / improved in SQL 2008? How about you meet us half-way…. update the save dialog code to read from a registry setting, then let those of us annoyed by this issue go tweak that setting… "]
It is a shame that Microsoft trains us as developers to provide a rich user experience by allowing configurations, remembering last options, etc. but then ingores it’s own advice in the products. I would like to be able to save the result file in Query Analyzer without having to remember to click the tiny down arrow on the “Save” button (what a strange place!) in order NOT to save the file as unicode. Why can’t we just have a default setting in options, or default to ANSI, or at least a registry value? Why is that so difficult? It’s like Query Analyzer makes it very easy to make a human error by default!<=-=Sep 8 2010 7:42AM=-=>
It’s 2010 now and this issue is ridiculous.<=-=Jan 12 2011 7:30PM=-=>
It’s 2011 now and this issue is ridiculous.<=-=Jan 31 2011 4:06PM=-=>
Not having the default easily configurable is an enormous and unnecessary problem. The U.S. isn’t the only market, so we’re not asking for UTF-8 to be an uncofigurable default either. But it’s ridiculous to be forced to deal with the unnecesary overhead, complexity, errors and frustration of having to use encoding (UTF-16) that is often unneeded in U.S. applications. It doubles the storage space and complicates/breaks interaction with source control, yields false diffs & unneeded versions when going from UTF-8/ASCII to UTF-16/Unicode or vice-versa, and makes it impossible to view scripts in VSS.
“Save with encoding” is terribly inefficient, requiring extra clicks and scrolling through an unbearably long list of encodings to get to what should have been the default in the first place.
MS is clearly trying to push us toward Visual Studio (which in 2010 isn’t a bad option — great for development, still not viable for DBAs) instead of SSMS, and toward TFS (much more viable w/ the new licensing, but still not an option for many shops) instead of VSS. Build all the functionality of SSMS into Visual Studio and put a bullet in SSMS. I’d then gladly trade-in the crippled pseudo-extended-VS platform of SSMS where add-ins require hacks and are still a roll of the dice, and half-hearted project and source-control support and move into VS full-time.
NOTE on SourceSafe: 2005 still chokes on Unicode — if you do a diff, it will report it binary and is unable to display the script. And if one version is unicode and the othe ASCII, it will report them as different. If you set VSS to save these as text rather than binary, the comparison/viewing will be virtually unreadable — the second byte of every unicode character will display as a filled block.<=-=Jan 12 2012 10:14AM=-=>
And now it’s 2012 and this is still a pain in the rear.<=-=Jun 6 2012 8:22AM=-=>
This makes Microsoft SQL Server Manager useless to me. I actually copy and paste from this tool into a REAL text editor that knows that I want to save as UTF8 or ANSI and never as UTF16.
It’s now June of 2012. This was broken in 2006, and is still broken SIX YEARS LATER.<=-=Apr 30 2013 11:37AM=-=>
Happy FIFTH birthday to this issue!
It’s odd, Red Gate’s SQL Multi Script saves to a format that SSMS2012 sees and immediately wants to change to “my way!”. Who’s right? My money’s NOT on MS!<=-=Sep 18 2014 2:39AM=-=>
Belated SIXTH Birthday for this issue. It is still a problem that has wasting developers time in SQL 2012.<=-=Feb 26 2015 5:06AM=-=>
The problem still exists in SSMS 2014!<=-=Jul 7 2015 6:48AM=-=>
Any chance of this getting fixed with SQL Server 2016? How hard would it be to add a configuration setting (even if it is just a registry entry or XML node) so we can have the ability to set the default encoding of files? This causes a major pain with source control systems, code review tools, etc.<=-=Jul 8 2015 1:58PM=-=>
Please see the workaround I posted that worked for SSSMS 2014! Should work with other version where International Settings can be changed!<=-=Jul 15 2015 10:43AM=-=>
git recognises default unicode file as binary, since it’s full of 0’es. Please introduce default encoding, so that we do not choose UTF-8 without signature every freakin time! You cannot drag for that long, since we are programmers, we do know how long it takes to implement these sort of changes! Outrages..<=-=Jul 21 2015 4:40AM=-=>
Yea MS, it can’t be more than a 3 line change right!<=-=Aug 7 2015 5:24AM=-=>
Unbelievable – 7 years later, and the bug is still not fixed :-O<=-=Apr 15 2016 4:11PM=-=>
Happy 8th birthday. Want to let you guys know that Fakher Halim’s workaround DOES seem to work for me with SSMS 2014.<=-=May 4 2016 10:22AM=-=>
The “Same as Microsoft Windows” workaround actually saves as the Windows-1252 encoding for most of us, which is fine if you only plan to use 7-bit ASCII characters in your .sql files, but step outside that with something like � � �� � � � � � � � � � � (curly quotes are the worst) and all your tools that assume UTF-8 aren’t going to like it.<=-=Mar 8 2017 4:36AM=-=>
SSMS 2016 st