Cannot resolve collation conflict for equal to operation

You set up your linked server, you write a query which joins two tables, you execute the query and the error message is this
Cannot resolve collation conflict for equal to operation


What does this mean? This mean that the collation on the two tables is different

Let's look at an example. Le's create two tables, onme with Traditional_Spanish_CI_AI collation and one with the default. The default collation for me is SQL_Latin1_General_CP1_CI_AS.


CREATE TABLE #Foo (SomeCol varchar(50) COLLATE Traditional_Spanish_CI_AI)
CREATE TABLE #Foo2 (SomeCol varchar(50))


INSERT #Foo VALUES ('AAA')
INSERT #Foo VALUES ('BBB')
INSERT #Foo VALUES ('CCC')
INSERT #Foo VALUES ('DDD')

INSERT #Foo2 VALUES ('AAA')
INSERT #Foo2 VALUES ('BBB')
INSERT #Foo2 VALUES ('CCC')
INSERT #Foo2 VALUES ('DDD')

Now run this query and you will get the error message

SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol


Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Now add COLLATE Traditional_Spanish_CI_AI to #Foo2 SomeCol

SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol COLLATE Traditional_Spanish_CI_AI

That works, if you add COLLATE SQL_Latin1_General_CP1_CI_AS to #Foo SomeCol that will work also


SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol COLLATE SQL_Latin1_General_CP1_CI_AS = f2.SomeCol


If you want to know what these collations mean then run the following query (yes that is not a typo it is indeed ::).

SELECT *
FROM ::fn_helpcollations()
WHERE name in('SQL_Latin1_General_CP1_CI_AS','Traditional_Spanish_CI_AI')

Traditional_Spanish_CI_AI
Traditional-Spanish,
case-insensitive,
accent-insensitive,
kanatype-insensitive,
width-insensitive

SQL_Latin1_General_CP1_CI_AS
Latin1-General,
case-insensitive,
accent-sensitive,
kanatype-insensitive,
width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data