Far Cry
Not having a decent story can be forgiven in most first person shooters if the action is hot and heavy, which it was.. Until the Tri-Gens. Promptly uninstalled, and haven't looked back.
Unfortunately, I'm going through a phase where I must finish everything... Far Cry is going to be torture to get through.. If I do.

Several parts of the NWN2 OC.
The ending of MotB, which wouldn't let me do what I wanted... (I found this very disappointing because I'd got myself worked up in favour of the faithless...)
Ultima 8 - the jumpy bits! Thankfully better once it was patched, but why did they have to choose that whole action/adventure route? Made worse by this actually having some great moments.
Bioshock - the reveal, and almost all that followed. Did not live up to the hype.
Oblivion/Fallout 3 - Had the possibility to be great (and had the hype and promotion to go with it) but failed imho.
C&C3, RA3 - I'd rather play the originals for the nth time.
Half-Life - the alien levels at the end...
HL2 - the boat and driving sections. Seemed a bit unnecessary, and felt a bit repetitive. Spoilt my opinion of an otherwise good game. (I was also a bit disappointed by the lack of AI improvement, especially for the random resistance people. However, thats not a HL2 specific problem, just seems like AI hasn't improved since HL1...)
SQ6, KQ7, QfG5 - Just in general.
Final Fantasy Legend (Gameboy) - I could never defeat "Creator" at the top of the tower... I guess I'm more disappointed in myself!

Oblivion - after playing Morrowind to death and seeing some screenshots of this game, I had high hopes. Oblivion turns out to be massively cut down, dumbed down and the scaling system... urgh!
Deus Ex Invisible War - Again, I loved the prequel. Again I was disappointed to find the sequel was dumbed down console fodder. Can't hold a candle to the predecessor. The bastard thing also thought that constant disc accessing just to make sure that the game isn't being pirated... you know, pirated while I'm playing the damn thing... yeah that really helped keep the game running smoothly. Even on my present computer that can run most modern games maxed out struggles with this one just because of that.
Deus Ex 3 - Still a long way from being released and already we've been told that it's yet more dumbed down console fodder. Careful! You might almost be required to think!
Bioshock (retail version) - As the brackets would indicate, it's not the actual game that disappoints me, but the malware they included in the name of DRM in the retail version. My anti virus thought it was a virus. When I looked it up online, the AV was right, it was a virus! I took it back and I got the Steam version for £3 earlier this year. So that's 90% of the money I paid they missed out on because of that shit.
STALKER Clear Sky - The first STALKER was great. Unfortunately it was released unfinished because they ran out of money. But when patched up it was a great game. Why the hell the sequel was released in a similar state, I don't know. But add to that the activation limits and I was laughing at how shit it was. Never bought it, but still a disappointment just reading about it.
Quake 4 - Most of the game wasn't too bad, if a little uncreative, but the ending was utter crap.
DRM (2007 - Present) - Nothing else has restricted my buying decisions like the retarded attempts by publishers to 'protect' their games from their paying customers, while giving pirates a free ride. I could list numerous examples of such disappointments, but I'm sure you're familiar with the list already.

I read that article written recently about that evil A.I., and I doubt that ANY game could live up to a story like that. At the same time, I feel like the game's being given held a little higher than it deserves to be. I had no expectations of BioShock being some heir to Awesome Town, so I enjoyed it a lot.
As for disappointing experiences, I've had a few:
Hellgate: London: Bought that huge box set, only to find it sucked. This is the last time I get excited because something's called a successor to Diablo II!
Most first person rpg's that aren't Oblivion or Fallout 3: I hated how easily you could screw up your entire game in Deus Ex: Invisible War, and since I've hard most first person rpg's are like that, I decided to stick with Bethesda's new titles for the most part.
My PS2: It can't play PS1 games or PS2 games made on a compact disc. Now that I have a PS3, I can play the few PS1 games I bought before I realized this, but I can't play PS2 games on the PS3.
Skate 2: Skateboarding in this game feels very satisfying, but you constantly have to hear your annoying cameraman yell things like "Bangin'!" and "Holy crap!". Worse yet, there isn't an option to specifically shut him up.
Gitaroo Man: I love this game's style, but I can't get past that Goddamned blues bee. It's just too punishing.
Far Cry 2: Taking pills constantly while having to get more after they run out is the dumbest game mechanic ever. At least in Oblivion it gave you the option to handicap yourself like this, instead of forcing this on you!
Crazy Taxi: Why did you have to be on a compact disc? You worked perfectly for a while, but then my PS2 stopped playing them. I had so much fun with this game...
Final Fantasy VII: For a game that people tout as having the greatest storyline ever, its' story sure is average, with the "greatest" part dealing with how emotional the characters are. A bunch of people whining about how they aren't strong enough is not what I call gripping story.
Twisted Metal: Head-On for PS2: Instead of putting in some levels for the cancelled Black 2, why not just give me Black, especially since the Black 2 levels are not good?

I despised the jumpy bits! Thankfully, I have the patched re-release of Ultima 8, but even then, the jumping sections were more than rough around the edges. I spent many a moment trying to pixel hunt a section where I could either, land the jump, or grab the damn ledge.
Overall though, it was an awesome game.

The ending of KotOR 2.
Also Spore.

Spore didn't really have an ending, the game just keeps going and going. KOTOR2 I couldn't even play past after I escaped the planet, so boring compared to KOTOR1. Mine would have to be Beyond good and evil.

Tekken 3
The game was great, but after spending way too many hours in practice mode, not to have fun, but to work on kicking ass more proficiently, I thought to myself "dude, if you worked that determined on stuff like school, reading or learning languages or instruments, you could actually achieve something. instead of playing games." It was a actually a very sad realization.
Eye of the Beholder
I had fond memories of Might & Magic 3-5 (looks similar to a kid), always loved the screenshots of this one and haven't played it until a few years ago. It was not what I expected.
The Gamecube in general
I don't buy games that often and when I buy a console, it's usually a used one, so I'm starting out quite a bit into the console's lifespan in general. This was the first time I found no interesting games on my current system. And roughly half of the games I actually bought were kinda disappointing to begin with.
Deus Ex - Invisible War
After reading this thread, this probably isn't that surprising. However, I didn't play the first outing and I really liked IW at the beginning. Unfortunately the game gets worse with every minute.
The Rescue of Princess Blobette
It was a gift. Worst christmas ever.
Mass Effect
The first run was just a chore. However, this was with standard Shepard paragon-style and the game is kind of strange in that regard. Second run with a female renegade adept Shep was a blast. Well, there's still the Mako sections ....
Alone in the Dark - The New Nightmare
This one was actually surprisingly enjoyable and a good time has been had by all involved. Unfortunately the last part of the game switches from well executed survival horror to half-assed shooting sequence. One of the poorer design choice that could have been made.
The Legend of Zelda - Ocarina of Time
Only played Link's Awakening before and loved it. Since it's usually regarded as one of the weaker Zeldas, while OoT dominates Top 10 lists all around the world, needless to say, there was minor excitement involved. I'm obviously in the minority, so maybe I just have bad taste, but it turned out to be one of the worst games I've ever played. If we leave out games, that are actually broken and unplayable, and just focus on stuff like story and design, it easily falls all the way to the bottom.

For me Red Alert 3 no where near as good as Red Alert 2 game just sucked.
The pc game of terminator (released at same time as third film just cant remember its name) Worst game ever no story ai was rubbish graphics suck felt like a mod for some crap game.
star trek legacy was so looking forward to that only to get a game with 1/2 the story missing and crap controls my defiant was as responsive as a turd in space.
Never winters nights 1 like others here was so looking forward to it then when it came out got bored within an hour and went to play a new rpg called morrowind.
KOTR 2 the end nuff said on that

When I realized that levels in Thief 3 were divided in half by a swirling blue load screen.
Thief 3 was still a good game (not as good as the first two), but between the garish interface that was clearly meant to be viewed from a couch twenty feet away and the obvious desperation to fit the maps into a tiny amount of memory, it is quite possibly the most screamingly obvious console port I have ever played.
The community uncovered many ways to tweak the interface to make it much more bearable, though.

It makes sense because you are in Kelemvor's realm and he just won't let you do that. Even if you are powerful, he is a lot more powerful than you are. He says himself that he can't act against the Spirit Eater directly, because it's a hollow thing, but he can definitely destroy your army with a single thought and you have no hope of even harming him yourself. It might be disapointing but idealism often gets hit by a reality check, and this is it. Allowing you to kill Kelemvor and destroy the Wall would have cheapened the story. The devs said that they thought of giving you the option to try anyway, but it could only result in a fight that couldn't be won at all, letting you wrongly think that you might actually be able to do it, or a cutscene where you see your character failing — like if you decide to join the Unity in Fallout and get dipped, which results in a game over — but any of the two would have probably annoyed the other half of the player base so ...
Even if you never played SSH2, the game is still mediocre. The combat is still easy for everyone — shock + wrench stays the most effective way to dispose of enemies during the whole game — the game is still designed around the fact that your character will die and get no penalty for that, the choice with the little sisters is loaded because you get the best rewards for doing the good things, and except the ending cinematic chosing one option or the other won't change much — Tennenbaum will still help you even if you harvested each and everyone of them, and the story is easily predictible. Still it has a great artistic direction and atmosphere and definitely deserves the praise for that.

It's interesting how many of these are based on people's expectations, rather than reality. (usually in terms of existing games or legacy they are expected to live up to).
I'm in the same boat, as mine would be:
X-Wing vs tie-Fighter:
Loved the first games, and was so excited about a game with real textures etc... yet somehow i missed the fact it was totally multiplayer focused, and when i installed it and found there was no story... sigh.
Jedi Outcast:
Loved Dark Forces, and particularly Jedi Knight so much. Bought JK2 only to find it was a generic game that had no excellent features at all. It got good reviews, so it probably wasn't bad on it's own terms, but after jedi knight....
Doom3:
Now I actually enjoyed Doom3, and i think it gets way too much flak... but it sure didn't have a doom feel, and it wasn't in the same class as doom2.
Plus i upgraded my PC just for it... yet i played Farcry straight after it (which i see people mentioning above, but don't understand) and it was the best FPS i'd played since HL.
Final Fantasy 6:
Been wanting to play it for ages, and the intro and beginning were awesome... but the random battles made it unplayable and i gave up after a few days. Shame, as i'd love to have played through the story.
Oddly, i wasn't at all disappointed by theif3, Deus Ex2, driv3r, etc.. i think it's because i played them a year or so later, so i'd heard about their flaws and didn't expect too much. the flaws were all true, but had been amplified by expectations. With no expectations I enjoyed them all.
(as for Spore... i think the people who didn't like it weren't the target market...)

The problem is that the people who didn't like it were precisely the target market until some EA exec suddenly said "No no, that won't do at all. Now dumb it down and cuteify it until it appeals only to 13-year old girls". Before that happened, it was marketed towards science geeks primarily.

Most endings - actually, that applies to books and movies as well. I don't like good stories to end (or the window I'm viewing everything through to close), I always want to know what happens next (yes, even if the main characters are dead or whatever, how about the others? the rest of the world?).