Monthly Archives: September 2011

How to get Gum out of MyHair

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.
MyGum table
As you can see, MyGum is a nice gum product table that lists gum types, brands & colours, etc.

MyHair table
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 …
Table join error
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 :

So, let’s look at the collation on these 2 tables
MyHair MyGum collation
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 ?
Table join success

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.

Clear recent project list in BIDS

So, you’ve been working, working, working in Business Intelligence Development Studio (BIDS), which is a variant of Visual Studio, and you’ve been cranking out some really awesome BI projects. Problem is that you REALLY don’t want to close and reopen your BIDS because now you have a loooong list of recent projects to scan through to find the one that you want to work on.

Click NOW !!

Click NOW !!

Well, have I got a deal for you …

If you click on this registry file, you will receive the solution that you have been looking for. This will clean that list of recent projects Like NEW !!

Here is the registry entry for the project that will be cleaned out, if you prefer to do it manually :

Perhaps your list of recently opened files is getting too extensive. If you click on this registry file in the next 18 minutes, I will throw the file list cleaner in COMPLETELY FREE !!

Here is the registry entry for the file list that will be cleaned out, if you prefer to do it manually :

Act now to save yourself valuable minutes and possibly hours of frustration EVERY DAY !!

Disclaimer: Professional coder on a closed system. Typical results, not experienced by everyone. If you have a project or file list longer than 40 items, call Devin Knight.