I can't even remember the last time I played a rpg with a great story(Digital Devil Saga may have been going somewhere, but I got distracted by something or other and never got a chance to finish it). You may have some cool characters here and there and a good scene now and then, but never enough to come together to the total package. It's all ranged to bad and cliche to..decent and cliche. I mean, I liked the dragon quest 8 story and all the characters for example, but when I picked up the controller it was because I wanted to play the game. Never once because I was anxious to 'See what happened next'.
Or Grandia III which I'm playing now, that Penny Arcade comic and the rant that went with it had me prepared for the worst. I was expecting something terrible, instead I've just got the usual. Most of the people making these games clearly don't care about the story, they're just drawing from a already existing template and not even trying to spice up the cliche story with their own unique style or anything.(Though going back to DQ8 to it's credit it did have a bit of it's own unique style, it's far more 'fairy tale' then other rpgs and I think that helped it a lot)
Going back to that penny arcade thing, comic-http://www.penny-arcade.com/comic/2006/02/22
and a quote from the rant.
The stories in most of the JRPGs we get are fucking garbage. Is this a controversial statement? Only the most dominated nihongophile recoils, straining on his Eastern leash. These "stories" are challenges in an of themselves: like a hulking boss creature, they are trials against which the human mind must strive. Exhausting existential retreads that course through the meat of the brain like poison.
There's exceptions to the rule, but in general if you like rpgs with a good story, well...you're kinda screwed. Maybe 5% of them will actually provide it.