What are the specific issues you have with the idea of having a concrete, known goal for the campaign?
The most difficult issue is maintaining player interest.
While playing through the RttToEE mega-module, there weee times that I simply no longer cared about reaching the end. It was long, painful, and at times, completely inane. My DM and I worked together to have our own personal mini-campaign set up outside the Temple when the dungeon crawl got too tedious, otherwise I would have quit the Temple about a third of the way through. The game would have continued, to be sure...
... But what if the DM has a homebrew campaign and the grand, sweeping uber-plot is completely ignored or bypassed by the players? the DM must then decide the ramifications (if any) of ignoring his carefully-crafted plot, and must now create new adventures for the players. Less savvy DMs will have likely not planned for such a possibility.
Now, my DM (the one from the example) has an epic uber-plot as the backdrop of his homebrew world. He likes the storytelling style of Tolkein, where the World Continues As It Does regardless of what the characters may or may not do. If Frodo had given up the ring quest, Sauron's army would still have been assembled, great wars still would have happened, Smaug still would have been killed, etc. In the same way, the characters I play are a part of the world, but can choose to step into and out of "The Plot", but no matter what I do, Something Will Happen. In some ways, it makes me (as a player) feel powerless, that I can make my mark on the world but said mark barely scratches the surface of what's Really Going On.
Personally, I prefer to run games the other way. The PC's are the heroes, the movers and shakers, and it is the world that is created around and for them. Stuff Happens, of course, but most things only change if/when the PC's interact directly with the scene. YMMV, of course.