今天在做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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-616845/