Recently when I was referring to SQL Server Forums I saw a question that reads like:
I get an error Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
What is a Collation?
In simple words Collation is the set of rules that ensure proper use of characters for Language or Alphabet. Here are some recommendation from books online about choosing collation:
- Select a BINARY2 collation if binary code point based ordering is acceptable.
- Select a Windows collation for consistent comparison across data types.
- Use a new 100-level collation for better linguistic sorting support.
- If you plan to migrate a database to the upgraded instance of SQL Server, select the collation that matches your existing collation of the database.
The error that we are referring here shown up because the two databases have different collation for some reason, now, to overcome this issue one may use COLLATE clause, but do remember that COLLATE clause will work with CHAR,VARCHAR,TEXT, NCHAR, NVARCHAR and NTEXT data type.The Syntax :
SELECT * FROM SYS.databases SD JOIN SOMEDB.DBO.SOMETBL JC ON SD.database_id = JC.DBID COLLATE Latin1_General_CI_AIHere are some external resource for further reading