SQL – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

This error occurs when trying to join two databases that have different collation.

you can change the collation of a database using the method listed here:
http://robbamforth.wordpress.com/2009/11/13/sql-%e2%80%93-change-the-collation-of-a-database/

If changing the collation of the database isn’t an option then you can force a collation by specifying it in the SLQ statement.

The following code would use the default collation:                    

–  SELECT
select a.*, b.*
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 
and a.field2 = b.field2

– UPDATE
update [DATABASE1]
set FIELD = B.FIELD
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 
and a.field2 = b.field2

The following will force SQL_Latin1_General_CP1_CI_AI collation:

–  SELECT
select a.*, b.*
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 COLLATE SQL_Latin1_General_CP1_CI_AI
and a.field2 = b.field2 COLLATE SQL_Latin1_General_CP1_CI_AI

– UPDATE
update [DATABASE1]
      set FIELD = B.FIELD
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 COLLATE SQL_Latin1_General_CP1_CI_AI
and a.field2 = b.field2 COLLATE SQL_Latin1_General_CP1_CI_AI

 

SQL – change the collation of a database

 

The following code shows how to alter the collation of a database.
The database must be set to single user mode to run the update. Remember to set the database back to multiuser mode if required.

ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DATABASE] COLLATE Latin1_General_CI_AS
ALTER DATABASE [DATABASE] SET MULTI_USER