By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

UTF-8 Coding of MSSQL 2005 DB

P: n/a
Hi List,

I am searching franticly for a solution (or the procedure) to setting
the coding of a new DB to UTF-8. I can find no setting in the Server
Manager, during creation of the DB, to influence this. Can someone
please show me the way? Thanks

--Shawn
Mar 26 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
See the following artciles:

Description of storing UTF-8 data in SQL Server (the article points to SQL
7/2000, but 2005 still uses UCS-2)
http://support.microsoft.com/kb/232580

UTF8 String User-Defined Data Type
http://msdn2.microsoft.com/en-us/library/ms160893.aspx

International Features in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/bb330962.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Mar 26 '08 #2

P: n/a
Hi Plamen,

Thanks for your answer.
See the following artciles:

Description of storing UTF-8 data in SQL Server (the article points to
SQL 7/2000, but 2005 still uses UCS-2)
http://support.microsoft.com/kb/232580
I found this one, but could not really understand the meat of the mater.
UTF8 String User-Defined Data Type
http://msdn2.microsoft.com/en-us/library/ms160893.aspx

International Features in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/bb330962.aspxB
Basically, there is no native UTF-8 storage then in MSSQL, am I right? I
mean, the scripts that we are using in perl are writing to the DB in
UTF8, and the displayed information in the webapp is returning properly,
but when you look at the data in the table, it is displayed
incorrectly. Does that seem right?

I am speaking of storing German charachters like ""

--shawn
Mar 26 '08 #3

P: n/a
On Mar 26, 11:11 am, Shawn Beasley <shawn.beas...@freenet.dewrote:
Hi List,

I am searching franticly for a solution (or the procedure) to setting
the coding of a new DB to UTF-8. I can find no setting in the Server
Manager, during creation of the DB, to influence this. Can someone
please show me the way? Thanks

--Shawn

I have converted my data from Latin 1 to UTF-8 in MSSQL.

The first thing to realise is (as has already been mentioned), MSSQL
uses UCS-2, a 2 byte Unicode encoding scheme. Not UTF-8, but we can
still use it.

What I did was create another table with an ntext column type (instead
of the old text used for Latin 1, of course your column in question
may be varchar etc, so you will need to create a table with a column
of type nvarchar etc).

What I then did is create a script to pull the data out of the old
table and insert it into the new table with the ntext column.
For each line I pulled out I used a function to encode it into UTF-8
(I was using php, maybe you could use
Convert::Scalar::utf8_encode() ? ). For that line I executed an SQL
statement to insert the line into the new table, however I prefixed
the Letter 'N' to the value of the newly UTF-8 encoded string. This
tells the db to treat the string as a unicode constant and not to
apply any of its own formatting/encoding to the string.

Now the newly encoded values in your new table may look like garbage
through the MSSQL gui, but the data is correctly encoded in UTF-8 and
will display in a webpage with UTF-8 content-type headers.

Maybe there is an easier way to do it, but this worked for me.

Hope that helps at least a little.
Raymond
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.