We must not forget the adventure with the Seven Deadly Jeks. I think it was a Side trek, but it was a TPK. At first, my players were unimpressed.
Party: "kobolds? c'mon, I thought we were past that stuff. Alright guys, lets kill them quickly so we can get to the main adventure." I bought the Complete Humanoids Handbook just for that one. All my players wanted to be kobolds or goblins after that.
I also loved "Thyandar's(sp?) Legacy". It just kept going and going, it had a run in with a previous adventuring party of NPCs, and all sorts of unrelated monsters. Giants, a black dragon, undead (I think), and others. Great mixture of puzzles and combat.
One more. I thought the one with the flying prismatic castle was just awesome, too. I ended up changing it a little to give each color a more "elemental" feel, but the story rocked just as it was written.
edit: spelling mistakes