ERROR: Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation

If you get the following SQL Server error message:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation.

It is because of Collation settings on two columns you are joining on (or maybe something similar. There are permanent fixes, but are not simple and mostly assume you can change table definitions, etc.

I prefer the simple fix.  

The first step is to figure out what are two collations that are conflicting. Assuming that the collation has not been set at the column level and is instead at the database level, here s what you need to do.

Execute the following two statements (after you put your two database names in the statements).

select DATABASEPROPERTYEX ( 'DB1', N'Collation' )
select DATABASEPROPERTYEX ( 'DB2', N'Collation' )

If you are on SharePoint you will likely get Latin1_General_CI_AS_KS_WS. If you are on most any other database and use the default settigs you may get: SQL_Latin1_General_CP1_CI_AS.

Now that you know what you are dealing with you just need to do something similar to a CAST, but for Collation. It is called Collate. Here is an example of a query that joins on two columns that have different collations.

select * from DB1.dbo.User u
join DB2.dbo.UserMap m
on (u.email = m.email COLLATE SQL_Latin1_General_CP1_CI_AS)

For your query you will should choose the Collation of one of the columns.

If you want to understand more about this, I suggest checking this link: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx