Possible spoilers for newer fans, so beware.
I'm sorry to say that I don't really look forward to Stargate anymore. Out of all the sci-fi worldsettings out there, Stargate's is one of the most depressing: the human race at large is scattered across the galaxy, used primarily as nothing more than cattle for the Go'auld. Earth is presented as being one of the more advanced human worlds, yet they remain largely inferior to the Go'auld; those human sects that have technology more advanced than that of Earth are either extinct, on the way to -being- extinct, or possess some wishy-washy pacifistic philosophy that prevents them from supporting the military effort against the Go'auld.
The non-human races aren't much help either. The Ascended and the Nox are loathe to intervene in human affairs. The Tollan are essentially dead, and the Asgard are doomed to lose their long-standing war against the unstoppable Replicators. The Tok'ra are in a similar situation, gradually losing more members of their group, and the majority of the Jaffa are too steeply indoctrinated in Go'auld dogma to make a stand for themselves.
SG-1 is written in such a fashion as to never resolve these issues, but instead, to drag them out as long as possible. I've seen spoilers for the upcoming season, and in my opinion, it's just more of the same pseudo-political moral high-ground stuff that's been thrown at us for the last several seasons. For that reason, there can never be any closure, and SG-1 will either be canceled before a finale is penned, or the finale itself will be a weak episode with a "Scorched Earth" ending, something along the lines of all Earth's Stargates being destroyed, or the Sol system being sealed off from the rest of the galaxy.
As for Atlantis, the very concept makes me cringe.
