If you have differente collation in two columns, and you try to make some kind of query with one of these things:
1) Join predicates
2) Where clauses
3) Use more than one database or temporal tables (for example: TempDB with different collation than the other database).
The error message is:
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Modern_Spanish_CI_AS" in the equal to operation.
(Latin1_General_CI_AI and Modern_Spanish_CI_AS are collation examples).
See the example to resolve collation column conflict:
Test1.Col1 ->Latin1_General_CI_AI (DATABASE_DEFAULT)
You can set the COLLATE to Query:
SELECT ID FROM Test1
INNER JOIN Test2
WHERE Test1.Col1 = Test2.Col1 COLLATE DATABASE_DEFAULT (or COLLATE Latin1_General_CI_AI)
Another options are set the same Collation to any level column, database, or servers.
To see the collation database, go to properties database, or use this:
SELECT DATABASEPROPERTYEX('DATABASE', 'Collation') as DB;