Today 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 4Cannot 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_helpsortexec 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 Rjoin 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 Rjoin DYNAMICS.dbo.SY01400 U on U.USERNAME collate Latin1_General_CI_AS_KS_WS = R.UserName
select R.UserName from ReportServer$SQL2008R2.dbo.Users Rjoin 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 Rjoin 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.
12-Dec-2011: Add example with DATABASE_DEFAULT collation.collation.
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.
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
The MSDN books online link is already in the article.
Posting from Jivtesh Singh at About Dynamics, Development and Life
Posting from Mark Polino at DynamicAccounting.net
Thanks. Exact solution for my exact problem. Gob bless.
Excellent Post. Same answer as other posts, but better explanation and understanding of the issue.
tnx very much! good answer.
Excellent Work David. Very helpful post. thank you :)
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.