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:


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


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

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.


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

Comments (9)

  1. Jon Eastman says:


    It can be more useful to use the generic COLLATE DATABASE_DEFAULT as this means that you don't have to work out the exact collation on the database.


  2. mgomezb says:

    COLLATE is not a SQL command, it's a SQL clause. For more information on the COLLATE clause take a look at SQL Server Books Online at msdn.microsoft.com/…/ms184391.aspx.


    Mariano Gomez, MVP

  3. David Musgrave says:

    Hi Mariano

    The MSDN books online link is already in the article.

    David 🙂

  4. David Musgrave says:

    Posting from Jivtesh Singh at About Dynamics, Development and Life


  5. David Musgrave says:

    Posting from Mark Polino at DynamicAccounting.net


  6. Franco says:

    Thanks. Exact solution for my exact problem. Gob bless.

  7. ConcernedCitizen says:

    Excellent Post.  Same answer as other posts, but better explanation and understanding of the issue.


  8. TriberConsultant says:

    tnx very much! good answer.

  9. Arfan says:

    Excellent Work David. Very helpful post. thank you 🙂