Collation difference between SQL 6.5 server and SQL 2000 server

Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency =3D N'=C6=CE=DD'
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!I would sugest running the VB app in debug mode and see what happens when
the VB app issues the select statment. In addition at the same time you may
open Profiler.
The collation you are using in sql 6.5 works well for you so I would try to
keep the same collation with sql 2000.
Be aware that with sql2000 you have collation at three levels: server,
database and columns.
Verify that you have installed sql 2000 with the desired collation.
Yaniv
--
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1135763223.233337.224300@.g49g2000cwa.googlegroups.com...
Hello,
We are facing the following problem:
We have installed SQL 6.5 server Enterprise edition on Windows 2000
Advanced server. On this system, in the regional settings, the Locale
is Japanese and in Office 2003 Language settings also, we have added
Japanese.
We have another system with Windows 2000 server on which we have
installed SQL 2000 server Enterprise edition with SP4. On this system,
in the regional settings, the Locale is Japanese and in Office 2003
Language settings also, we have added Japanese.

>From the front end VB code, a query is formed which contains Japanese
value. This works in the SQL 6.5 environment but does not work in the
SQL 2000 environment. If we directly execute the query in SQL 2000
server, it is returning the row. But the VB front end is not
recognizing the row.
The query is like this:
SELECT * FROM Tstar_Currency WHERE Currency = N''
This query works when the VB client connects to the SQL 6.5 server and
the front end fetches one row. But when the VB client connects to SQL
2000 server, the front end is not fetching any row. However, if we
directly execute this in query analyzer in SQL 2000 server, it returns
one row.
Any idea why this is happening? What collation setting should we give
in the SQL 2000 server? Do we need to do anything in the front end
application?
Any help will be greatly appreciated. This is very urgent. Please also
mail to ravi.meenakshi@.gmail.com
Thanks!|||(Ravi.Meenakshi@.gmail.com) writes:
> We have installed SQL 6.5 server Enterprise edition on Windows 2000
> Advanced server. On this system, in the regional settings, the Locale
> is Japanese and in Office 2003 Language settings also, we have added
> Japanese.
> We have another system with Windows 2000 server on which we have
> installed SQL 2000 server Enterprise edition with SP4. On this system,
> in the regional settings, the Locale is Japanese and in Office 2003
> Language settings also, we have added Japanese.
>
> value. This works in the SQL 6.5 environment but does not work in the
> SQL 2000 environment. If we directly execute the query in SQL 2000
> server, it is returning the row. But the VB front end is not
> recognizing the row.
> The query is like this:
> SELECT * FROM Tstar_Currency WHERE Currency = N''
> This query works when the VB client connects to the SQL 6.5 server and
> the front end fetches one row. But when the VB client connects to SQL
> 2000 server, the front end is not fetching any row. However, if we
> directly execute this in query analyzer in SQL 2000 server, it returns
> one row.
Wait a minute, that query does not even have legal syntax for
SQL 6.5. Is the N really there?
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
If you run sp_help on Tstar_Currency on SQL 2000, which is the
data type, and which is the collation for that column?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The N is there in the query only in SQL 2000, not in 6.5
The data type of Currency column in Tstar_Currency in SQL 6.5 is
char(20) and in SQL 2000 is nchar(20)
The collation of this column in SQL 2000 is Japanese_CI_AS
The only change in the VB code is the inclusion of N before the
Japanese value.
Any clues on how to solve the problem?|||I ran profiler for the 2000 environment. In the query I first saw in
the profiler, I saw boxes. Then I changed the font in the Profiler to
Japanese. Then the query displayed like this:
SELECT MSCICode FROM Tstar_Currency WHERE Currency =3D N'=EF=BE=86=EF=BE=8E=
=EF=BE=9D'
When I executed this query in query analyzer, it did not return any
rows.
Please note that the client PC on which the VB application is running,
the regional settings is changed to Japanese.
I can't understand why the Japanese value was not passed correctly from
VB to SQL Server.
Any clues?|||(Ravi.Meenakshi@.gmail.com) writes:
> The N is there in the query only in SQL 2000, not in 6.5
> The data type of Currency column in Tstar_Currency in SQL 6.5 is
> char(20) and in SQL 2000 is nchar(20)
> The collation of this column in SQL 2000 is Japanese_CI_AS
> The only change in the VB code is the inclusion of N before the
> Japanese value.
> Any clues on how to solve the problem?
I asked you these questions:
What does sp_helpsort return on SQL 6.5?
On SQL 2000, what does SELECT Serverproperty('Collation') return?
It's difficult to give clues, if I don't get any.
But it does sound funny to me that the sole difference should be the
addition of the N. If the characters in the string are Unicode, it would
not work on 6.5. Since it works on 6.5, I suspect that the string is in
some other character set. In which case it will of course not work with
a Unicode column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The output of sp_helpsort in 65. is:
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
The output of SELECT Serverproperty('Collation') in SQL 2000 is
SQL_Latin1_General_CP1_CI_AS
The collation setting of the SQL 2000 server is the default setting.
The collation for the database that the front end code is accessing is
Japanese_CI_AS|||(Ravi.Meenakshi@.gmail.com) writes:
> The output of sp_helpsort in 65. is:
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase
> Case-insensitive dictionary sort order for use with several We
> stern-European languages including English, French, and German
> . Uses the ISO 8859-1 character set.
> The output of SELECT Serverproperty('Collation') in SQL 2000 is
It sounds like a miracle if you get Japanese text correcly into that
server. Or at least a great deal of luck.

> SQL_Latin1_General_CP1_CI_AS
> The collation setting of the SQL 2000 server is the default setting.
> The collation for the database that the front end code is accessing is
> Japanese_CI_AS
I have not really managed to fully understand at what collation the
client APIs looks at when they convert data between character sets,
but I suspect that's the server collation. In which case Japanese text
that is the ANSI code page, will be mangled before it's sent to SQL Server.
Apparently you are building a an entire command string in VB. Try to use
parameterised command instead, as then you can declare the type of the
parameter.
You could also try to install an instance of SQL 2000 that has
Japanese_CI_AS as its server collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql