Sql server中Collation conflict問題

今天在做data migration時寫了一個跨資料庫查詢的語句：

select d.ID as SGPID,'S' as SGPTYPE, 1 as UpUser, GETDATE() as UpDT, number

from [DataMigr_V33].[dbo].[_upd_dts_timedef1] c, [DataMigr_V4].[dbo].[adDTStore]d

where storenum<>0 and c.storenum  = d.StrNumber

結果在執行時遇到這樣一個問題，問題描述：

Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

到網上搜了一下，瞭解了問題產生的原因，原來是兩個資料庫的collation不一致導致的，DataMigr_V33用的是SQL_Latin1_General_CP1_CI_AS，而DataMigr_V4用的是Chinese_PRC_CI_AS，知道原因，解決起來就好辦了。將查詢語句改成如下：

select d.ID as SGPID,'S' as SGPTYPE, 1 as UpUser, GETDATE() as UpDT, number

from [DataMigr_V33].[dbo].[_upd_dts_timedef1] c, [DataMigr_V4].[dbo].[adDTStore]d

where storenum<>0 and c.storenum collate Chinese_PRC_CI_AS = d.StrNumber

問題就解決了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-616845/，如需轉載，請註明出處，否則將追究法律責任。