Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?

Question

I'm trying to store Japanese characters in nvarchar fields in my SQL Server 2000 database.

When I run an update statement like:

update blah 
set address = N'スタンダードチャ'
where key_ID = 1

from SQL Server Management Studio, then run a select statement I see only question marks returned to the results window. I'm seeing the same question marks in the webpage which looks at the database.

It seems this is an issue with storing the proper data right? Can anyone tell me what I need to do differently?

Answer 1

You need to set the collation correctly on your database.

You need to check the globalisation settings of all the code that deals with this data, from your database, data access and presentation layers.

(You also need to work out which version you are using, 2003 doesn't exist...)

Answer 2

SSMS will not display that correctly, you might see question marks or boxes

paste the results into word and they should be in Japanese

In the webpage you need to set the Content-Type, the code below will display Chinese Big5

<META HTTP-EQUIV="content-type" CONTENT="text/html; charset=big5">

To verify the data you can't use ascii since ascii only can see the ascii character set

run this

select unicode(address),ascii(address) from blah where key_ID = 1

Output should be the following (it only looks at the first character) 12473 63

Answer 3

This cannot be a correct answer given your example, but the most common reason I've seen is that string literals do not have a unicode N prefix.

So, instead of

set address = N'スタンダードチャ'

one would try to write to a nvarchar field without the unicode prefix

set address = 'スタンダードチャ'

See also: N prefix before string in Transact-SQL query

Answer 4

I can almost gurantee that the data type is not unicode. If you want to learn more you can check Wikipedia for information on Unicode, ASCII, and ANSI. Unicode can store more unique characters, but takes more space to store, transfer, and process. Also some programs and other things don't support unicode. The unicode data types for MS SQL are "nchar", "nvarchar", and "ntext".

I really hope I was able to help. If you want anything else I'd be glad to help. I don't know if this website will update me by email; if you want to contact me you can use [email protected]