Freigeben über

SQL Server Error: Cannot resolve the collation conflict

  • Artikel

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.

Comments

  • Anonymous
    December 08, 2011
    David, 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. Jon.

  • Anonymous
    December 08, 2011
    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. MG.- Mariano Gomez, MVP

  • Anonymous
    December 11, 2011
    Hi Mariano The MSDN books online link is already in the article. David :-)

  • Anonymous
    December 14, 2011
    Posting from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../everything-dynamics-gp-49.html

  • Anonymous
    December 14, 2011
    Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com/.../sql-server-error-cannot-resolve.html

  • Anonymous
    February 22, 2012
    Thanks. Exact solution for my exact problem. Gob bless.

  • Anonymous
    August 21, 2012
    Excellent Post.  Same answer as other posts, but better explanation and understanding of the issue. Thanks.

  • Anonymous
    November 04, 2012
    tnx very much! good answer.

  • Anonymous
    April 09, 2014
    Excellent Work David. Very helpful post. thank you :)