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.

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