SQL Server调用MySQL数据源时使用参数出现排序规则冲突如何解决

问题根因 #

你遇到的是跨数据源字符排序规则冲突问题：通过OPENQUERY从MySQL的PLMStock数据源返回的LIBELLE字段在转换为varchar类型时默认继承了数据源的French_CI_AS排序规则，而SQL Server本地表PROFILE的PRF_NAM字段使用的是Latin1_General_CI_AS排序规则，两种不同排序规则的字段做模糊匹配时就会触发该报错。硬编码参数时SQL Server会自动将硬编码字符串转换为和匹配字段一致的排序规则，因此不会报错。

解决方案 #

有两种常用的修复方式，选择其中一种即可：

方案1：在模糊匹配时显式指定统一排序规则 #

直接在LIKE条件中对其中一个字段强制指定排序规则，统一使用其中一种即可，示例修改对应行如下：

WHERE t2.Libelle COLLATE Latin1_General_CI_AS LIKE pr.PRF_NAM + '%'

如果需要保留French排序规则的匹配逻辑，也可以反过来转本地字段：

WHERE t2.Libelle LIKE pr.PRF_NAM COLLATE French_CI_AS + '%'

方案2：从OPENQUERY读取数据时就指定统一排序规则 #

在转换字段类型的同时指定排序规则，避免后续匹配时重复处理：

SELECT 
    CAST(t1.CLETRI as varchar) CleTri, 
    CAST(t1.LIBELLE as varchar) COLLATE Latin1_General_CI_AS Libelle
FROM OPENQUERY("PLMStock",'SELECT CLETRI, LIBELLE FROM produits') t1 

修复后完整查询示例（采用方案1） #

select sp.SLC_ID, a.ASS_NAM, p.PCE_NAM, p.PCE_DES, pr.PRF_NAM,
(
    SELECT top 1 t2.CleTri
    FROM (
        SELECT CAST(t1.CLETRI as varchar) CleTri, CAST(t1.LIBELLE as varchar) Libelle
        FROM OPENQUERY("PLMStock",'SELECT CLETRI, LIBELLE FROM produits') t1 
    ) t2
    WHERE t2.Libelle COLLATE Latin1_General_CI_AS LIKE pr.PRF_NAM + '%'
) as CLETRI
from SELECT_PART sp
left join PART p on p.PCE_ID = sp.PCE_ID
left join ASSEMBLY a on a.ASS_ID = sp.ASS_ID
left join PROFILE pr on pr.PRF_ID = p.PRF_ID
where sp.SLC_ID = 2930 and p.PRD_ID is null

内容的提问来源于stack exchange，提问作者Eric Brochu