SQL Server 错误“隐式转换，因为值的排序规则由于排序规则冲突而无法解析." [英] SQL Server error "Implicit conversion of because the collation of the value is unresolved due to a collation conflict."

问题描述

我在开发存储过程时遇到这个错误

I getting this error while developing stored procedure

无法执行 varchar 值到 varchar 的隐式转换，因为由于排序规则冲突导致值的排序规则未解析.

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

语句是这样的

Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
UNION ALL
Select '' AS Place, 'Arizona' As State, Country FROM DEPT2

但是如果这样做也会出现同样的错误

but If If do this it also give same error

 Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
 UNION ALL
 Select '' COLLATE DATABASE_DEFAULT AS Place, 'Arizona' As State, Country FROM DEPT2

实际上这段代码是别人写的，只是在编辑代码，不知道为什么他添加了 COLLATE DATABASE_DEFAULT 但如果我删除它也会出现同样的错误

Actually this code is written by some one else and am just editing the code, do not know why he added COLLATE DATABASE_DEFAULT but If I remove it also gives the same error

无法执行 varchar 值到 varchar 的隐式转换，因为由于排序规则冲突导致值的排序规则未解析.

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

推荐答案

您很可能在两个地方都需要 COLLATE.

You'd need COLLATE in both places most likely.

Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
UNION ALL
Select '' COLLATE DATABASE_DEFAULT AS Place, 'Arizona' As State, Country FROM DEPT2

如果你把它放在一个地方，你可能需要在每个字符串上使用它

You may need it on every string if you get it in one places

Select
    City COLLATE DATABASE_DEFAULT AS Place,
    State COLLATE DATABASE_DEFAULT AS State,
    Country COLLATE DATABASE_DEFAULT AS Country
FROM DEPT1
UNION ALL
Select
    '' COLLATE DATABASE_DEFAULT,
    'Arizona' COLLATE DATABASE_DEFAULT ,
    Country COLLATE DATABASE_DEFAULT
FROM DEPT2

编辑 2:

发生这种情况是因为您的列排序规则可能与您的数据库排序规则不同.所以City"有一种排序规则，而字符串常量有另一种排序规则.

It happens because your column collation is probably different to your database collation. So "City" has one collation but string constants have another.

这篇关于SQL Server 错误“隐式转换，因为值的排序规则由于排序规则冲突而无法解析."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！