collation conflict

[newgen pras]

Hello friends ..
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
[SQLServer]Cannot resolve collation conflict for UNION operation.
I am getting this error ... can any one help me ..

[edited subject - was "Help pls .."]
[ June 17, 2005: Message edited by: Jeanne Boyarsky ]

[Paul Sturrock]

A "collation" in SQL Server is a definition of how character data may be sorted and compared (for example, whether case plays a part in either operation). Typically a SQL Server database is created with one collation for all tables so you can compare two fields with character datatypes no bother. But, if you try to do a join on two character fields with different collations you will run into problems since the rules for how each field in compared are different. I'd check the DDL for each table in the join - get SQL server to generate the DDL, it will include the collation definition. If they are different you should change the DDL. As a last resort you can define the collation for the join, for example:

select ... from ...
where field_1 COLLATE Latin1_General_CI_AS = field2 COLLATE Latin1_General_CI_AS

[newgen pras]

Thanks for ur response paul..
I guess question i posted is not more specific .. See my actual problem .. I am debugging a software .. for that thing the database should be in chineese collation ..

First time i chaged the database collation using 'ALTER' command..but the tempdb and other system thing remaind in English
collation .. so the error came ..

After that i had completly removed the database and reinstalled the database fully in chineese ...but still i am getting the error .. I don't know how to proceed ... I need suggestion about how to proceed ..

[Paul Sturrock]

TEMPDB is created every time you start SQL Server, and it is created with whatever collation the model database uses. What that is is dependent on how the server instance is configured, not how you create each individual database. I can't help you with changing the collation for model - I assume its probably an install option, but I've never had to do it. Check the collation of model, and try explicitly setting it to match when you make the join.

[newgen pras]

Thanks for ur help Paul....