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 ”起作用。