Home Dashboard Directory Help
Search

Need a way to set the default encoding for query files in SMSS. by Chris May


Status: 

Active


82
0
Sign in
to vote
Type: Suggestion
ID: 336750
Opened: 4/3/2008 8:51:14 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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.

Details
Sign in to post a comment.
Posted by SAinCA on 4/30/2013 at 11:37 AM
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!
Posted by Warren in Canada on 6/6/2012 at 8:22 AM
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.

Posted by MikeGalusha on 1/12/2012 at 10:14 AM
And now it's 2012 and this is still a pain in the rear.
Posted by rathor on 1/31/2011 at 4:06 PM
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.
Posted by Yicone Wong on 1/12/2011 at 7:30 PM
It's 2011 now and this issue is ridiculous.
Posted by Denis Valeev on 9/8/2010 at 7:42 AM
It's 2010 now and this issue is ridiculous.
Posted by kellysql on 6/28/2010 at 8:39 AM
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!
Posted by gocheif on 1/26/2010 at 2:23 PM
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)
Posted by Brian Perry on 9/28/2009 at 2:54 PM
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.
Posted by danholmes on 5/6/2009 at 8:02 AM
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.
Posted by danholmes on 5/6/2009 at 7:26 AM
Does anyone know if an SSMS addin would be able to intercept the save operation and force a change to the codepage?
Posted by Simon L-Deslauriers on 1/26/2009 at 8:32 AM
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).
Posted by paulo999 on 1/26/2009 at 6:05 AM
This also causes problems in Rational Clearcase source control - its native version diffing functionality doesn't support Unicode.
Posted by L Smith on 12/8/2008 at 1:30 PM
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?

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...
Posted by nwheaton on 7/10/2008 at 8:53 AM
Being unable to set the default file format is a big drawback to using SMSS. Is there no registry hack to get around this?
Posted by TobyKraft on 6/13/2008 at 9:00 AM
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.

Thanks,

Toby
Posted by Microsoft on 4/8/2008 at 10:17 AM
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
Sign in to post a workaround.