Yesterday, I was working on SQL Server 2008 R2 for a Proof of Concept regarding map reports. I was doing some querying on the database and hence, had to join a table in a SQL 2008 DB and SQL 2008 R2 DB. So for this purpose, I created a linked server and executed the following query. SELECT *
FROM mapdata AS a
LEFT OUTER JOIN
CHAOS.diw.dbo.DIM_SALES_ORGANISATION AS b
ON a.[Sales Rep] = b.[employee_name];
That was when I hit upon the following error “ Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. ” 
On further reading, I found out that the default collation in R2 is SQL_Latin1_General_CP1_CI_AS and in SQL 2008 is Latin1_General_CI_AS. Since the databases have different collation, it would not be possible to compare the fields from those databases directly. To solve this issue, I just added the following keyword before the = operator like shown below SELECT *
FROM mapdata AS a
LEFT OUTER JOIN
CHAOS.diw.dbo.DIM_SALES_ORGANISATION AS b
ON a.[Sales Rep] COLLATE database_default = b.[employee_name] COLLATE database_default;
Collation conflict error
