Management Studio - text encoding problem

[jjefferson]

I can't find the setting to tell the new SQL Server Management Studio Express to "save as" plain ol' text (like the old Query Analyzer used to do) instead of this new fangled two-byte Unicode text. The exported text imports just fine into Unicode-aware things like Excel, but we have some old apps that can't be rewritten that are choking on the output.

A search through BOL didn't yield anything helpful, but I could just be looking in all the wrong places. Thanks for any advice. I'd hate to have to add an interim process to alter the text file.

Thanks!
Jim

 

[Catadmin]

Are you talking about resultsets or the Tools - Options - General in the 2000 QA where you set the extensions?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

If you're talking about the Results to Text, you can find it in SMS under QUERY -> Results To -> Results in Text.

Under Tools -> Options, there is a Text Editor set which you can set extensions & such, including a Plain Text "tab".

Does that help you out?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jjefferson]

Catadmin, Thanks but I'm able to get the results out to a text file okay. My problem is that it is saved as Unicode text and not ANSI text. QA has an option in the "Save As" dialog to select the file format (ANSI, Unicode, or OEM (cp437)), but I can't find a similar option in SMS anywhere.

To illustrate, in QA the ANSI file format would save "ABC" as "65 66 67" decimal or "41 42 43" hex. SMS saves this text as "65 00 66 00 67 00".

Unicode-aware apps (Excel, etc.) don't have a problem, but our old DOS apps that have to read these files can't handle the extra NUL byte per character (not without extensive modification).

So, there's where I'm stuck. I can't find where to change the encoding for results being saved. I'd rather not have to perform some interim step outside of SMS to re-encode the file.

Thanks for the help!

Jim

 

[Catadmin]

Jim,

I found the following in 2k5 BOL, but I'm not sure if it'll help because I can't find what it is referencing.

Use this dialog box to select a language encoding scheme and to specify the characters to be inserted at the end of each line. To access this dialog box, open a code file for editing in SQL Server Management Studio, and then click Advanced Save Options on the File menu.

Options
Encoding
Use this list box to apply a new language encoding scheme to the target file.

Line Endings
To ensure that the target file will run on a specific operating system, select whether a carriage return (for Macintosh systems), a line feed (for Unix systems), or both characters (for Microsoft Windows systems) will mark the end of each line.

I'll keep looking, but ISTR something during my SQL 2k5 courses that the change was made to Unicode because of all the XML and CLR stuff that was added to SQL 2k5.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jjefferson]

This seems to be on-point and I'll search for this. I must have glossed right over this in my search of Books Online. Thanks very much for the reference.

I've been worried that an all-Unicode world is coming. This may be the ammo I need now to convince them to replace the DOS apps, but they've been bulletproof for so many years, get the job done, and have huge amounts of credibility, so it will be a hard sell.

But, I have to solve the immediate problem and you've been a great help. I'll post back if this yields results.

Thanks!
Jim