主页 / dba / 问题 / 319523
Accepted
Martin Riddar
Asked: 2022-11-12 06:57:24 +0800 CST

创建存储过程时排序规则冲突

  • 772

我正在尝试创建一个存储过程，但它给了我错误消息
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

问题是数据库和服务器排序规则都是SQL_Latin1_General_CP1_CI_AS，我不知道Latin1_General_BIN2排序规则是从哪里来的。

create procedure [ETL_1.4.0].update_valve_event_type
(
    @data nvarchar(max)
)
as
    declare @mismatch_table table(id int, [name] varchar(50))

    if isjson(@data) = 0
    begin
        ;throw 50000,'Input argument @data is invalid JSON.', 1
    end

    insert @mismatch_table (id, [name])
    select * from
    (
        select
            value as id,
            [key] as [name]
        from openjson(@data)
        except
        select
            id,
            [name]
        from enum.valve_event
    )data

    --clear mismatches that are deemed ok, e.g. spelling corrections
    delete from @mismatch_table
    where id = 10 and [name] = 'FEEDBACK_FROM_USER'

    if exists(select * from @mismatch_table)
    begin
        ;throw 50000,'Terminal version of enum for valve events does not match the EDW version', 1
    end
go

那么有人可以解释我所缺少的吗？
如果重要的话，这个数据库（以及与此相关的服务器）还没有上线，所以从技术上讲，我可以随心所欲地更改排序规则。

sql-server

1 个回答

  1. Best Answer

    它可能是OPENJSON函数的输出，特别是因为它是EXCEPT操作的一部分。您需要做的就是通过COLLATE选项/关键字在该查询中强制排序。例如：

    ...
    [key] COLLATE SQL_Latin1_General_CP1_CI_AS as [name]
from openjson(@data)
...

    至于强制使用哪种排序规则，请选择一个支持您希望操作如何运行的排序规则（简单来说：应该'A' = 'a' 或 'A' <> 'a'）。两个最可能的选项是已经使用的两个排序规则：Latin1_General_BIN2, 和（在这种特殊情况下）SQL_Latin1_General_CP1_CI_AS。（是的，如果你选择Latin1_General_BIN2哪个已经是key列的排序规则，你仍然需要指定COLLATE选项，如上所示，以避免错误。）

    OPENJSON()的文档甚至松散地声明输出使用BIN2排序规则（尽管它没有指定确切的排序规则，并且我不打算引用它，因为描述不完全正确）。

    最好的选择是只询问 SQL Server。我们可以通过将输出列存储在由 SQL Server（通过SELECT INTO ...）动态创建的表中来验证输出列的排序规则，然后检查新创建的表中列的属性：

    SELECT *
INTO #CheckCollation
FROM OPENJSON(N'{}'));

SELECT [name], [system_type_id], [collation_name], [is_nullable]
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb..#CheckCollation');

-- [key] column has collation of Latin1_General_BIN2
-- [value] column has collation of passed-in JSON value:
--     if passing in column, then collation of column, OR
--     if passing in variable or literal, then default collation of database

    或者，@Charlieface（在对此答案的评论中）很好地提醒我们，有一种更简单的方法可以通过使用酷的内置动态管理功能来获取此信息sys.dm_exec_describe_first_result_set，并通过db<>fiddle提供了一个工作示例. 我更新了该示例以包括该函数的存储过程版本，sys.sp_describe_first_result_set以及上面显示的示例代码。

    • 2

相关问题