Khmer Unicode, English and Microsoft SQL Server 20

Question

I have searched the internet now for quite a while and found several bits but in total it's still not working.

I'm using Microsoft SQL Server 2008 SP2 for a database in which I need to write either Khmer Unicode or English letters.

According to this Blog here: http://sochinda.wordpress.com/2009/10/03/khmer-unicode-on-sql-server/

It is said that I need to use the collation SQL_Latin1_General_CP850_BIN for Khmer Unicode AND datatypes NCHAR, NVARCHAR or NTEXT as they are unicode compatible.

When I use the following SQL statement:

use pis

INSERT INTO dbo.pmd (patient_code, last_name, first_name, age, sex, province, district, commune, village) VALUES ('0600-075D4-4AC8', 'ៃាំុំឌគៃុំាំឌ', 'ៃគុំដសហគៃុំះកឆញហេឆ', '2008', 'm', '060000', '060400', '060403', '06040304')

The result for last_name and first_name in table dbo.pmd will look like this:

last_name: ????????????? first_name: ??????????????????

The rest of the values are alright.

So Right-Click on my database, click on properties, shows me that the collation is set to SQL_Latin1_General_CP850_BIN. Right-Click on table dbo.pmd, click on Design, shows me that last_name and first_name are defined as NVARCHAR(50).

So what do I need to do different to make the Khmer Unicode characters getting stored in the database?

And by the way: This SQL Example here, works like a charm... don't see the difference...(beside that this statement would use the standard collation which doesn't work for me either.

Answer 1

You need to use N on your strings to make them unicode

INSERT INTO dbo.pmd (patient_code, last_name, first_name, age, sex,province, district, commune, village)
VALUES ('0600-075D4-4AC8', N'ៃាំុំឌគៃុំាំឌ', N'ៃគុំដសហគៃុំះកឆញហេឆ', '2008', 'm', '060000', '060400', '060403', '06040304')

A quick example:

DECLARE @khmertest TABLE (
            SomeText nvarchar(50) COLLATE SQL_Latin1_General_CP850_BIN,
            SomeText2 nvarchar(50)
      )

INSERT @khmertest VALUES ('ៃាំុំឌគៃុំាំឌ', 'ៃាំុំឌគៃុំាំឌ')
INSERT @khmertest VALUES (N'ៃាំុំឌគៃុំាំឌ', N'ៃាំុំឌគៃុំាំឌ')

SELECT * FROM @khmertest

gives

SomeText    SomeText2
?????????????   ?????????????
ៃាំុំឌគៃុំាំឌ   ៃាំុំឌគៃុំាំឌ