Unicode support in 2005 - turn on or off?

Question

Post reply

Unicode support in 2005 - turn on or off?

Tom Williamson-285801

SSC Enthusiast

Points: 167
More actions
January 27, 2006 at 8:01 am

#166801

I'm experiencing grief trying to export code/data from 2005. Specifically, the output shows up in Unicode in the following cases:
1) Query results emailed to people using sp_send_dbmail are showing up in Unicode and can't be read by some recipients.
2) SQL code I script from my client machine using Management Studio has been defaulting to Unicode, which causes much unhappiness with our source control system (which is NOT, unfortunately, SourceSafe.)
I have discovered the "save with encoding" feature in Management Studio so I at least have a workaround for that, but how the heck do I tell the server not to save query results (and/or anything else) as Unicode?
Anyone have any ideas?
Thx in adv. -
Tom

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

pcwik SSC-Addicted Points: 404 More actions · Answer 1

Is your data stored in Unicode? Do you use nchar or char? If it's stored in Unicode you will send in Unicode.

Peter Cwik

Tom Williamson-285801 SSC Enthusiast Points: 167 More actions · Answer 2

None of the data should be Unicode. Everything's CHAR or VARCHAR. And the Unicode output affects not only data, but also scripting of T-SQL code and database objects like stored procs. Very strange and no way to turn it off, as far as I can tell.

pcwik SSC-Addicted Points: 404 More actions · Answer 3

The next question is...What coallation is set on your database?

Tom Williamson-285801 SSC Enthusiast Points: 167 More actions · Answer 4

SQL_Latin1_General_CP1_CI_AS is what's set right now, and I think that's the default collation. I don't think anyone's messed with it - ever.

jonwolds Right there with Babe Points: 718 More actions · Answer 5

I also have this problem. Server collation is Latin1_General Bin. Has anyone found a solution?

Thanks

Jon

arbarnhart SSC Rookie Points: 33 More actions · Answer 6

We have this problem also. If anyone has found a solution, I would really appreciate a posting of it. We would be okay with forcing all scripts to be Unicode as a workaround. I have noticed that some of the scripts have extended properties that can't be save without Unicode. Opening in Word and trying to Save As to change the encoding will show any characters that can't be encoded in flat text.

Interesting - I show as a "Newbie" who joined in 2002...

Tom Williamson-285801 SSC Enthusiast Points: 167 More actions · Answer 7

>>Interesting - I show as a "Newbie" who joined in 2002...

Sounds like a database problem to me...

arbarnhart SSC Rookie Points: 33 More actions · Answer 8

Newbie is probably based on number of posts; I mostly read articles, get scripts and find my questions have alrready been asked and answered. This time it has been asked but not answered.

I found a major clue on this issue. I have a table that scripts out as Unicode. One of the fields is nvarchar(max). If I chage that to ntext, then it scripts as flat text. There is another field in the table that is nvarchar(255), so it is not just the nvarchar throwing it off.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

I am thinking the problem maybe related to .NET types being Unicode by default, here is a page that shows all the code pages with associated Locale ID so that may help in finding the correct configuration settings. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms143508.aspx

Kind regards,
Gift Peddie

arbarnhart SSC Rookie Points: 33 More actions · Answer 10

Gift Peddie,

Thanks, but I don't think that is quite it. To be clear, it is not the scripting of data we are talking about. It is the table creation script. Changing the field type just changes a few characters in the script. And it is only nvarchar(max) that does it; if I set an arbitrary limit like nvarchar(255) then the table creation scripts as ASCII.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 11

If you are getting the problem with Varchar (max) then the Locale ID is important because it stores the overflow after a ROW in the file system of the OS as Text data type, it just comes with settings that trick SQL Server to do ROW based SET functions without the restrictions of Text data type. The links below explain what I am talking about. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms173530.aspx

http://msdn2.microsoft.com/en-us/library/ms186981.aspx

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Kind regards,
Gift Peddie