I'm getting the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.
I've been through the usual suspects and the column is set to Latin1_General_CI_AS, the table is set to Latin1_General_CI_AS and the database is set to Latin1_General_CI_AS.
It's a simple EXCEPT statement:
SELECT oj.[Key] AS GameType FROM Staging.ETL_Holding AS eh CROSS APPLY OPENJSON(legalities) AS oj WHERE eh.legalities IS NOT NULL GROUP BY oj.[Key] EXCEPT SELECT gt.GameType FROM Cards.GameTypes AS gt
I can add COLLATE Latin1_General_CI_AS to the game type and it will work, however, I feel I shouldn't need to as the column etc is of the correct collation already.
My Google-FU isn't bringing anything back. I've dropped and re-built the DB completely several times (it's dev DB for a home lab).
The create table statement is:
CREATE TABLE Cards.GameTypes ( GameTypeID TINYINT IDENTITY(1, 1) CONSTRAINT pk_Cards_GameTypes_GameTypeID PRIMARY KEY CLUSTERED NOT NULL, GameType VARCHAR(50) NOT NULL, Created DATETIME2 CONSTRAINT df_Cards_GameTypes_Created DEFAULT (SYSUTCDATETIME()) NOT NULL, IsActive BIT CONSTRAINT df_Cards_GameTypes_IsActive DEFAULT (1) NOT NULL ); GO
Any thoughts, I can work around it but I really don't want to and if I do, I'll never know what the issue is!
Answer by Kev Riley ·
Ignore that. The collation of the datatype for [Key] column returned by OPENJSON is Latin1_General_BIN2 : https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017#return-value