SQL Server Error: Cannot resolve the collation conflict

David Meego - Click for blog homepageToday I have a post on the solution to a tricky SQL issue.

When working with SQL Server and creating a join that links tables in two databases, all goes well when the two databases have the same collation. However, if the databases have different collations the query it will fail in a writhing blubbery gelatinous slimy mess of confused character mappings, or at least return something similar to the following error:

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

This is because SQL Server cannot compare character or text fields across different collations. This issue affects the char, varchar, text, nchar, nvarchar, and ntext data types.

I came across this issue yesterday on my system as I was writing some code that used the sp_MSforeachdb command (see related post: Running SQL commands against all GP Company Databases). I had a chat with my friend and resident SQL guru, Robert Cavill, and he helped come up with the solution.

It turns out that the ReportServer$SQL2008R2 and ReportServer$SQL2008R2TempDB databases created on my SQL Server 2008 R2 instance have a different collation to the system default and the rest of the databases.

If you run the following SQL commands you can see the collation information for the system and the databases:

exec sp_helpsort
exec sp_helpdb

Note:  For the database collation, look in the status column for the Collation property. 

An easier method could be to retrieve the collation property directly with the following commands:

print convert(varchar(max),SERVERPROPERTY('Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('ReportServer$SQL2008R2', 'Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('DYNAMICS', 'Collation'))
print convert(varchar(max),DATABASEPROPERTYEX('TWO', 'Collation'))

On my system, this returned the results below, which shows the report server database has a different collation:

Latin1_General_CI_AS
Latin1_General_CI_AS_KS_WS
Latin1_General_CI_AS
Latin1_General_CI_AS

So if I create a query that joins between the Report Server and DYNAMICS databases it will fail with the error message above, for example:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME = R.UserName

The solution is to use the COLLATE command to cast the collation of a character or text field into a specified collation. For more information about the COLLATE command, have a look at https://msdn.microsoft.com/en-us/library/ms184391.aspx

Using the COLLATE command I can force the collation for the fields used in the join expression to match, you can either change the first to match the second or the second to match the first. For example:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME collate Latin1_General_CI_AS_KS_WS = R.UserName

Or

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME = R.UserName collate Latin1_General_CI_AS

Or (based on Jon's feedback in the comments) you can use DATABASE_DEFAULT on both sides to make sure they match:

select R.UserName from ReportServer$SQL2008R2.dbo.Users R
join DYNAMICS.dbo.SY01400 U on U.USERNAME collate DATABASE_DEFAULT = R.UserName collate DATABASE_DEFAULT

Which way around you you write the code would depend on which would need less COLLATE statements when the query was more complex.

The funny thing is that today, I had a support case which asked about joining data between Microsoft Dynamics GP and Microsoft Dynamics CRM when the databases have different collations. As I had just dealt with the same issue, I could give them the answer straight away. This is what prompted me to write the solution as a blog article for all to benefit.

David

12-Dec-2011: Add example with DATABASE_DEFAULT collation.
collation.