Sql server中Collation conflict问题

(转载自 http://www.cnblogs.com/michaelxu/archive/2009/10/19/1585775.html )

今天在做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

问题就解决了。