28 February 2011

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.
image
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

1 comments:

  1. Good explanation, this helped me resolve the issue I was facing. Thanks.

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri