http://www.sqlservercentral.com/blogs/steve_jones/2011/05/23/collation-conflicts-in-a-sql-server-join/

Printed 2014/04/20 07:21AM

Collation Conflicts in a SQL Server Join

By Steve Jones, 2011/05/23

I went to run this query recently:

select TOP 10 *
 from users a
   inner join Banned b
   on a.username = b.username

and got this lovely message.

collation

I’d seen that message before, so I knew what was wrong. The collations for the two tables were inconsistent. Since this was a database that was upgraded from another version of SQL, and uses objects from a third party, I wasn’t surprised that a specific collation was used. I had created the “b” table myself, using database defaults, and they didn’t match the object.

I did a quick search since I couldn’t remember the exact syntax for the clause to add to my query. I ended up at a friend’s blog, Pinal Dave’s SQL Authority, and read this post: Cannot resolve collation conflict for equal to operation.

The fix is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:

select TOP 10 *
 from users a
   inner join Banned b
   on a.username COLLATE DATABASE_DEFAULT = b.username

Worked fine, and I was on my way.


Filed under: Blog Tagged: sql server, syndicated, T-SQL
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.