Cannot resolve the collation conflict

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.

Solution:

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_AI

Here are some external resource for further reading




http://msdn.microsoft.com/en-us/library/ms143508.aspx>




http://blogs.msdn.com/b/qingsongyao/archive/2008/08/25/sql-server-2008-katmai-s-new-collations.aspx

photo credit: mdanys via photopin cc

Read More