Combination of English and Chinese characters in Microsoft SQLServer 2000

Hello,
We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
one of our databases, we have a table REQ with one of the fields
COMMENT being defined as TEXT. Now, when we try to view the COMMENT
field, it shows us only English characters whereas the Chinese
characters are displayed as ?.
Is it possible to store both English and Chinese characters in the same
column of a table? If yes, then how?
Thanks in advance.
New user.
It still does not work. Is there any other alternative?
On Nov 19, 1:48 am, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
> one of our databases, we have a table REQ with one of the fields
> COMMENT being defined as TEXT. Now, when we try to view the COMMENT
> field, it shows us only English characters whereas the Chinese
> characters are displayed as ?.
> Is it possible to store both English and Chinese characters in the same
> column of a table? If yes, then how?
Yes, it's possible, but with the information you have given, it's difficult
to give exact advice.
First of all, which character set do you use for Chinese? Unicode? Big-5?
If you use Unicode, you should use ntext, if you use Big-5 or any other
non-Unicode character set, you should use text.
Next question is find out where things go wrong. Do something like:
SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
If there are question marks in the selected substring which should have
been Chinese characters, what do you see in their place in the binary
string? If you see 3F, the Chinese characters have been stored as ?, and
there is a problem on input. If you see the codes for the Chinese
characters, it's a display problem.
If it is an input problem, it would help to know who the characters enters
the database.
Disclaimer: I have no experience of working with Chinese on my own.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I ran the query as u suggested and the output for the binary string
shows as 3F for the Chinese characters. If that means there is a
problem in input, how do I go ahead from here?
On Nov 21, 9:17 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> to give exact advice.
> First of all, which character set do you use for Chinese? Unicode? Big-5?
> If you use Unicode, you should use ntext, if you use Big-5 or any other
> non-Unicode character set, you should use text.
> Next question is find out where things go wrong. Do something like:
> SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
> If there are question marks in the selected substring which should have
> been Chinese characters, what do you see in their place in the binary
> string? If you see 3F, the Chinese characters have been stored as ?, and
> there is a problem on input. If you see the codes for the Chinese
> characters, it's a display problem.
> If it is an input problem, it would help to know who the characters enters
> the database.
> Disclaimer: I have no experience of working with Chinese on my own.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> I ran the query as u suggested and the output for the binary string
> shows as 3F for the Chinese characters. If that means there is a
> problem in input, how do I go ahead from here?
To start with, please answer a few questions:
1) How does the data enter the database? From a Windows client? From a
web client? Which API do you use?
2) Which character set does the client use for the data?
3) What is the collation of the text column? (You can view this with
sp_help.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thank you very much for all your help. The problem has been solved.
After changing the field from text to ntext in the database, the client
still had some problems. Even though East Asian languages were
supported, the Language for non-Unicode programs was English. When it
was changed to Chinese and the PC was rebooted, everything is all fine
now.
On Nov 26, 1:15 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> 1) How does the data enter the database? From a Windows client? From a
> web client? Which API do you use?
> 2) Which character set does the client use for the data?
> 3) What is the collation of the text column? (You can view this with
> sp_help.)
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx