3

I'm trying to create a stored procedure but it gives me the error message
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

The thing is that both the database and server collation is SQL_Latin1_General_CP1_CI_AS and I have no clue where the Latin1_General_BIN2 collation comes from.

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

So can someone explain what I'm missing?
If it matters, this database (and server for that matter) is not live yet so technically I have the ability to change collations wherever I want.

1
  • 1
    Martin: I have updated my answer to include info about which collation to force, clarify which collation is used for the value column of the returned table, and provide an easier method of discovering the collation of columns in the output from a table-valued function (as suggested by @Charlieface ). Please review. Nov 16, 2022 at 15:28

1 Answer 1

6

It is likely the output from the OPENJSON function, especially since that is part of an EXCEPT operation. All you need to do is force a collation in that query via the COLLATE option/keyword. For example:

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

As for which collation to force, pick one that supports how you want the operation to behave (in simple terms: should 'A' = 'a' or 'A' <> 'a'). The two most likely options are the two collations already being used: Latin1_General_BIN2, and (in this particular case) SQL_Latin1_General_CP1_CI_AS. (And yes, if you choose Latin1_General_BIN2 which is already the collation of the key column, you still need to specify the COLLATE option, as shown above, in order to avoid the error.)

The documentation for OPENJSON() even loosely states that the output uses a BIN2 collation (though it doesn't specify the exact collation, and I'm not going to quote it as the description isn't exactly correct).

The best option is to just ask SQL Server. We can verify the collation of the output columns by storing them in a table that is dynamically created by SQL Server (via SELECT INTO ...) and then checking the properties of the columns in the newly created table:

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 

Alternatively, @Charlieface (in a comment on this answer) was kind enough to remind us that there is an easier way to get this info by using the cool built-in dynamic management function, sys.dm_exec_describe_first_result_set, and provided a working example via db<>fiddle. I updated that example to include the stored procedure version of that function, sys.sp_describe_first_result_set, as well as the example code shown above.

2
  • 2
    COLLATE DATABASE DEFAULT might be wiser, in case it gets changed. Also sys.dm_exec_describe_first_result_set might be an easier way to find the collation of a result dbfiddle.uk/vZ_CWm82 Nov 13, 2022 at 10:10
  • 2
    @Charlieface Thanks for that feedback 😺. Re: DATABASE_DEFAULT, I realize that it's a fairly popular option, but I believe it's used more often than it should be. It's truly great for operations that need to be sensitive to the environment, especially when that environment might change or if the code gets deployed to various places that don't have a guarantee of using the same collation. However, a lot of operations need to behave consistently from an app perspective, regardless of environment. Re: dm_exec_describe_first_result_set, I had forgotten about that, so I updated my answer. Nov 16, 2022 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.