SQL SERVER 不同语言字符集的比较问题

环境：

window 7+sqlserver 2008

同一电台上有两个数据库DB1、DB2

DB1数据库字符集: SQL_Latin1_General_CP1_CI_AS

DB2数据库字符集: Chinese_Taiwan_Stroke_CI_AS

使用下面的查询时出错

select donor_no,donor_type=case when donor_type='I' then 1 when donor_type<>'' then 2 else 3 end,
name,chi_name,title=(select top 1 ItemTypeID from PropertySetting where ItemKey='ED002' and NameEN+'.'  =title ),
sex=case when sex='M' then 1 when sex='F' then 2 else 0 end,religion=case when religion='christian' then 1 when religion='catholic' then 2 else 3 end,
hkid,newsletter=case when newsletter='YES' then 1 when newsletter='NO' then 0 end,
email,home,office,mobile,other_phone_no,office_fax,add_1,add_2,add_3,add_4
from HOHCS_Lagacy.dbo.donor
where donor_no in(select donor_no from HOHCS_Lagacy.dbo.receipt where rec_date>='2007-04-01')
错误信息：

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.

百思不得其解，又想用下面语句修改数据库字符集，

alter database HOHCS_Lagacy collate Chinese_Taiwan_Stroke_CI_AS

结果失败。

 

后来网上查询不同数据库字符集之间如何查询，原来是这样，需要关键字“Collate Database_Default”转换字符集，把语句改成如下：

select donor_no,donor_type=case when donor_type='I' then 1 when donor_type<>'' then 2 else 3 end,
name,chi_name,title=(select top 1 ItemTypeID from PropertySetting where ItemKey='ED002' and NameEN+'.'   Collate Database_Default =title Collate Database_Default ),
sex=case when sex='M' then 1 when sex='F' then 2 else 0 end,religion=case when religion='christian' then 1 when religion='catholic' then 2 else 3 end,
hkid,newsletter=case when newsletter='YES' then 1 when newsletter='NO' then 0 end,
email,home,office,mobile,other_phone_no,office_fax,add_1,add_2,add_3,add_4
from HOHCS_Lagacy.dbo.donor
where donor_no in(select donor_no from HOHCS_Lagacy.dbo.receipt where rec_date>='2007-04-01')

结果查询成功！

总结：不同字符集之间比较，关键字“Collate Database_Default ”起作用。