SQL Management Studio… How NOT to save in Unicode format

A project I work on requires that we developers edit stored procedures and store the resulting script in a text file that we put in our source control tool. In our case, we use Microsoft SQL Server Management Studio to do the aforementioned editing and we use CA’s Harvest Change Manager as the corporate mandated code repository and source control tool.

Now if you were using another tool, Notepad or Query Analalyzer, for instance, you probably wouldn’t have the problem I’m going to outline. As a matter of fact, the problem I’m going to outline is probably pretty rare. But it exists and I found a solution, so I’m going to write about it.

As it turns out, whenever you “Save As” in Microsoft SQL Server Management Studio the default encoding for the file is Unicode – Codepage 1200. Yes, this is a new approach to saving your beloved stored procedures and no, it wasn’t done that way in the past. Further, I can’t find any notification that the “standard default” was going to be changing.

Anyway, Harvest doesn’t like Unicode files. So when you go to add the file to your Harvest project it won’t let you. Oh, the humanity! ANSI for everyone!

Now I may be in the minority here, but I originally failed to notice that on the “Save File As” dialog there is a small arrow on the right side of the [Save] button. Once I noticed it, I clicked it… Then I clicked “Save with encoding…” Now I’m happy again! Kind of…

The good thing is that this will let you save your file in whatever encoding you want. The caveat is that it will let you save your file in whatever encoding you want.

So how is the default set? I’ll leave that for another article… Mostly because I don’t know yet.

Update (04.11.2008): Thanks to Chris May for the following step by step instructions on how to overcome this issue (edited for formatting, the original version is comment #8):

I have found some information about this.

Though it is possible at the time a script is saved to change the encoding to ascii it is tedious. Here is the process.

1. Choose File\Save
2. Choose the name and folder to save the file then look really closely at the right edge of the “Save” button for a tiny arrow
3. Click that tiny Arrow and choose “Save with Encoding”
4. From the Drop list select the encoding you want (the default encoding is “Unicode – Codepage 1200″, which means “UTF-16″). I have been using “US-ASCII – Codepage 20127″
5. Hit OK and Save. Your files should now work just fine with Perforce, CVS, etc.

Additionally, Chris has voiced his feedback to the SQL team, I encourage everyone to go have a look and hopefully we can get a solution sooner rather than later… although the outlook is bleak.