Well, here is a fun issue I ran into this week at one of our clients that I’d like to chat about.
Now, I may not have much hair to get gum stuck in, but some people have had this happen to them before.
But, because I am a database professional and I live inside of computers, I’d like to look at a virtual gum-in-hair problem.
First, Let’s have a look at a couple of tables that I’ve got here.
As you can see, MyGum is a nice gum product table that lists gum types, brands & colours, etc.
The MyHair table has the names of a bunch of people with hair types, colours and … well, would you look at this, some of them have gum stuck in their hair.
Oh, dear. Let’s do a quick query of the 2 tables to see what kind of gum is stuck in their hair …
Uh, oh !! Error !!
“Wow, I’ve never seen a collation error when doing a plain join of 2 tables in the same database before. Isn’t the collation set at the server or database level ?” you might say.
Well, grasshopper, that is a good question. Collation can also be set at the column level. Here’s the article in Books Online that discusses column collation : msdn.microsoft.com
So, let’s look at the collation on these 2 tables
Would you look at that … some
bloody developer, dumb user, um, silly person has gone & changed the collation of the Gum_ID column.
O-Kay, that is easy to fix. All we need to do is right-click on the table in Management Studio and select “Design” from the drop down. Then, we look at the “Collation” property under “Table Designer” for the Gum_ID column. We delete the content to set the collation to database default or click on the ellipses to choose the specific collation that we would like. We click OK and click Save. Voila, we should be good to go. Let’s try our joining query again, shall we ?
And THAT is how to get a little bit of collation Gum out of MyHair.
WARNING : You will need to make sure that no other columns have funny collation as you will need to fix them all.
WARNING 2 : You will need to be aware of the collation of TempDB, too, as using and joining temp tables will cause this same issue.