Início / dba / Perguntas / 319523
Accepted
Martin Riddar
Asked: 2022-11-12 06:57:24 +0800 CST

Conflito de agrupamento ao criar procedimento armazenado

  • 772

Estou tentando criar um procedimento armazenado, mas ele me dá a mensagem de erro
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

O fato é que tanto o banco de dados quanto o agrupamento do servidor são SQL_Latin1_General_CP1_CI_ASe não tenho ideia de onde Latin1_General_BIN2vem o agrupamento.

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

Então, alguém pode explicar o que estou perdendo?
Se for importante, esse banco de dados (e servidor) ainda não está ativo, portanto, tecnicamente, posso alterar os agrupamentos onde quiser.

sql-server

1 respostas

  1. Best Answer

    É provável que seja a saída da OPENJSONfunção, especialmente porque faz parte de uma EXCEPToperação. Tudo o que você precisa fazer é forçar um agrupamento nessa consulta por meio da COLLATEopção/palavra-chave. Por exemplo:

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

    Quanto a qual agrupamento forçar, escolha um que suporte como você deseja que a operação se comporte (em termos simples: should 'A' = 'a' or 'A' <> 'a' ). As duas opções mais prováveis ​​são os dois agrupamentos já usados: Latin1_General_BIN2, e (neste caso específico) SQL_Latin1_General_CP1_CI_AS. (E sim, se você escolher Latin1_General_BIN2qual já é o collation da keycoluna, você ainda precisa especificar a COLLATEopção, conforme mostrado acima, para evitar o erro.)

    A documentação para OPENJSON() afirma vagamente que a saída usa um BIN2agrupamento (embora não especifique o agrupamento exato e não vou citá-lo porque a descrição não está exatamente correta).

    A melhor opção é apenas perguntar ao SQL Server. Podemos verificar o agrupamento das colunas de saída armazenando-as em uma tabela criada dinamicamente pelo SQL Server (via SELECT INTO ...) e verificando as propriedades das colunas na tabela recém-criada:

    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

    Como alternativa, @Charlieface (em um comentário sobre esta resposta) teve a gentileza de nos lembrar que existe uma maneira mais fácil de obter essas informações usando a função de gerenciamento dinâmico integrada legal sys.dm_exec_describe_first_result_set, e forneceu um exemplo de trabalho via db<>fiddle . Atualizei esse exemplo para incluir a versão do procedimento armazenado dessa função, sys.sp_describe_first_result_setbem como o código de exemplo mostrado acima.

    • 2

relate perguntas