SQL Cannot resolve the collation conflict between “xxx” and “xxx” in the equal to operation – Use COLLATE DATABASE_DEFAULT

When joining 2 tables from different databases, the following error might occur:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Danish_Norwegian_CI_AS” in the equal to operation.

This can happen when the 2 databases is created with different collations (language settings). The collation is used by the database to apply the correct sorting and comparison of fields, which is why a comparison of 2 fields with different collations cannot be executed.

If this SQL statement fails:

   select a.fieldA, b.fieldB
     from [DatabaseA].[dbo].[TableA] a
left join [DatabaseB].[dbo].[TableB] b
       on a.key = b.key

You should add COLLATE DATABASE_DEFAULT to the “on” statement.
This SQL statement will solve the collation issue:

   select a.fieldA, b.fieldB
     from [DatabaseA].[dbo].[TableA] a
left join [DatabaseB].[dbo].[TableB] b
       on a.key COLLATE DATABASE_DEFAULT = b.key COLLATE DATABASE_DEFAULT

MORE TO READ:

 

Advertisements

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.