I suppose Fallout/Arcanum/VtM:B quests will come up so I'll pick a couple other games.
-------------------
Dark Sun: Shattered Lands
Escaping the Pit: Removing yourself from the gladiator pens at the beginning of Dark Sun was very well-done. By plopping the party into an ever-worsening situation with several ways to escape, some involving trusting people who you don't know you can trust, Dark Sun essentially forced the player to explore his options by talking to people and figuring out what to do next. Furthermore, each option came with a trade-off: if you chose to take help from the fellow with "connections," you'd be dealing with an unsavory type who will screw you over; if you try to escape with the king of the pens, you'll have to do it before your party has reached high levels, making the fight to the surface more difficult; and if you try to flat out fight your way out, you better be tough. So essentially the player had choices, the choices come from getting to know his surroundings, and he isn't pushed hard in one direction or another.
Notaku's Errands: The quests Notaku gives you aren't especially interesting in and of themselves, but I loved the way that, if I remember correctly, they tied into events in the world. In particular, I believe you find out that the items you'd been gathering for Notaku were being used by Balkazar, e.g., to make powerful magic items. This just gave the world a feeling of being alive and connected.
Prelude to Darkness:
Detective Quest in the Barrier: My favorite quest overall in Prelude to Darkness is probably this quest. You go to the barrier city to find out that some thugs have disappeared and one has been killed. The murder is thought to have been committed by a rageful foreigner who has been imprisoned. He says he didn't do it, but there is no reason to believe him and none of the other thugs who might have seen things are around.
To figure things out, you have to go to local bars and the like, asking around for anyone who has seen anything or who knows who might've. What I love about the quest is that it is the player's initiative that determines if it is done and how it is done. You can get involved with the quest by the man in prison, which involves arranging a fight between the local police without the game telling you exactly what to do, or by a woman asking you to find a particular man. You can solve it by tracking down the family of one of the thugs and convincing them to give you his location or by uncovering the murder of another of the thugs and getting a mystic to contact his spirit. Everything along the way depends on the player choosing how to proceed and the events and details unfold beautifully and relate to the main quest.
-----------------------
So those are the first ones that come to mind for me. In general I like when solving a quest comes down to the player deciding to do it, when there are little details and unknowns to figure out, and when everything ties into a bigger whole.