question

Hi All,

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!