I was just thinking about this last night. Personally I think the best quests that keep me interested are quests that don't follow a set format, a quest with variation and discovery.

A lot of modern RPG games have taken to the concept of more task-like quests in which NPC characters send away the player to preform a menial task over and over again. An example is :

Quest 1 : "Please go collect 50 mushroom tops.
Quest 2 : "Please go collect 100 mushroom tops.
* Repeat *

These are liner, plain and utterly boring. What I like is the opposite. I like quests that give me a vague idea of what I need to do and then let me work out how to get it down. It's got less direction but still a great deal of flow and arguably more exploration.

An example of this would be to go question some guards about what they saw on the night of March 12th. It just lets them go out into the town and look around. Often they'll stumble across things that have nothing to do with the quest but they become part of the questing experience.

When I look back at the greatest quests I've been on in games, it's not always the quest goal that makes it great. It's who I met along the way and how we worked together.

My two cents on the topic.