I'm a picky gamer, so I have far more 'thought I'd like but didn't' games.
GURPS is the big one. I have attempted many times to play and run this one. I can pretty much echo everyone here so far. Great fluff, but playing it was less than exciting.
Prime Directive (not the d20 version). I'm a big Star Trek fan, and an even biger fan of Star Fleet Battles. But PD just doesn't do it for me.
Let's see, briefly: Army of Darkness, Starship Troopers (those two taught me to stay away from movie based games), Mechwarrior, Hackmaster, Shadowrun (though I had more fun with the older editions), and Cyberpunk 2020. Believe me, there are plenty more.
There are a few I thought I wouldnt like, but not many.
Ars Magica 1E. I picked it up because at the time at our LFGS it was the thing to buy and play. Liked the 1E rules, but once White Wolf got ahold of it...
Palladium Fantasy. I was playing Rifts a lot at the time and having a blast with it. I bought PF mainly as a class sourcebook. But the more I read, the more I wanted to run it instead.
Paranoia XP. I never managed to complete a session of Paranoia. Anyone whose played it knows what I'm talking about. So I saw the XP edition, and thought 'meh.' But once I gave it a chance...it turned out to be pretty good. Now if I could only convince some local players around here to give it a try.
Finally, I have a third category: Games you waffle on. I'll explain with my entry:
SilCORE. We wanted to try something different. I had Heavy Gear, which was an okay mech game, but not fantastic. And to top it off, its a dice pool system, something I and my gaming group usually despise. But we made a point of trying something we would normally dismiss out of hand, just so we could say that we would play just about anything. So we go get SilCORE, since its basically the engine Heavy Gear runs on, but with the freedom to use whatever setting we want with it.
We fell in love with it. Its quick to pick up, easy to build characters, and has been very easy to 'wing it' with. Everyone was impressed with the mechanics, at first.
Two months later, we came to realize that it is too simplistic. The rules just don't have quite enough meat to keep us happy. A few of the rules that are in there are even a bit annoying (I really dislike the vehicle construction rules).
So we started out skeptical, grew to like it, then realized it was too light.