SQL Server: Join tables from 2 databases, collation issue

If you want to inner join two tables from different databases, you might encounter some collation errors.

When I executed this query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode = [items].itemCode
where ProductDeleted = 0 

I got this message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So when I executed the following query, I found out that I had two different Collations:

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

I thought that it was because one database was migrated from SQL Server 2000 to 2005 to 2008 and the other one (database2) was from 2005 to 2008. But apparently the default collation depends on the region settings of your Windows installation and gets set when you install SQL Server.

Now you can change the collation of your database with various solutions which can be found around the internet, but I wanted to simply join two tables and do one update.

I had to Google a lot to find out, how to define collations when using an inner join. But here is the solution for my query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode collate SQL_Latin1_General_CP1_CI_AS = [items].itemCode
where ProductDeleted = 0 

So you can define collation on the fields which you join.  

By the way: this is a great resource for the different collations: http://msdn.microsoft.com/en-us/library/ms143515%28v=SQL.90%29.aspx

Hope that it helped you, it would have saved me a lot of frustrations and time.