Collation Issue

In SQL Server 2005 with SQL_Latin1_General_CP1_CS_AS collation, if you create a table:

create table TEST_TABLE

(
ID NVARCHAR(9) NOT NULL,
DESCRIPTION NVARCHAR(30),
);

Run the following insert statement from our Unicode application through ODBC on a system with U. S. English as the default code page:

insert into TEST_TABLE (ID, DESCRIPTION) values ('中文', '123');

The row was added properly. Now if you select * from TEST_TABLE, the row is properly returned to our app with the Chinese characters intact.

However, if you put the Chinese characters as part of the where clause:

select * from TEST_TABLE where ID = '中文';

It would not return anything.

On the other hand, if this is done on Oracle 10g or Access database, the row is returned properly with the Chinese characters intact.

So far we found two ways to fix it, but both involve tremendous risk for us:

1. Change the database collation to Chinese.However, this defeats the purpose of using Unicode as only one language (Chinese in this case) can be used with such configuration.In case we need to mix Chinese, Japanese and Korean, such configuration will not work.

2. Prefix the Unicode strings with N-prefix:

select * from TEST_TABLE where ID = N'中文'

This works perfectly but we have a few hundreds of such strings in our code and would be a nightmare to convert each one of them.

Is there any other solution for SQL Server 2005?

I am not sure that you will be able to accomplish what you are trying. You can change the collation at the column level, rather than the database level if you need to....or, you can add the COLLATE clause to each of your queries. Otherwise, I don't think that there is a really good way to get around it.

Tim

|||

Unfortunately, there are a few things with SQL Server that could use improvement.

UniCode strings 'should' always be prefixed with [ N ] -and especially if the sting contains UniCode characters not supported in the current codepage.

As the saying goes, "I feel your pain". You have have a bit of work to find the 'few hundreds of such strings' and makes the necessary changes.

sqlsql