Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_L及由于排序规则不同导致查询结果为空的问题

时间:2020-11-28 03:28:51

报错:Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_L

出错原因:

两个跨库的表的collation不一致

解决办法:在出错的任意列名后面加上collate chinese_prc_ci_as

例如:

USE DBA;
INSERT INTO SUBJECTDB.ProDefine.ProjectInfo
        ( Code ,
          ProjectCityCode ,
          CnName ,
          Creator ,
          CreateTime ,
          ValidStatus ,
          SortNo ,
 VersionNo,
          VersionStartTime 
         )
SELECT NEWID(),
  CITY.Code,
  INS.项目名称,
  'BDD',
  GETDATE(),
  '1',
  '1',
  '1',
  GETDATE()
FROM BDD_PROJECTINFO_SHIYAN_INSERT INS 
INNER JOIN SUBJECTDB.Common.City CITY ON
INS.项目城市 COLLATE chinese_prc_ci_as =CITY.CnName
WHERE INS.项目名称 COLLATE chinese_prc_ci_as NOT IN(SELECT CnName 
 FROM SUBJECTDB.ProDefine.ProjectInfo
 )