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:
https://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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s