I'm not great at writing reviews, so I'll just spout my opinions...
I loved this series at first. The adventure, the action, the comedy, the drama... I thought it was all brilliant. Anderson, Tapping, Shanks, Judge, Davis... I loved them all. Davis, it seemed, was the fifth-most important person in the cast. Not a big deal. But when his character (General Hammond) left at the end of the seventh season, and Anderson's character (Colonel O'Neill) moved from the field to the office, the quality of the series suddenly fell off a cliff. I don't know whether it's because Hammond was more important that I realized or what, but for some reason, after the seventh season, the series turned to ****.
The first seven seasons, though, were awesome. The movie Stargate seemed mediocre the first time I saw it, but it turned out to be, even if this wasn't the original intention, a brilliant setup to the series. I recommend that you watch the movie first, then watch the first season of the TV series, then watch the movie again (you'll have a whole new appreciation of it the second time around, believe me), and then watch the rest of the TV series.
The last three seasons of the series aren't nearly as good as the first seven, but that doesn't mean they aren't good. It just means they're a letdown if you've gotten spoiled by the first seven seasons.
After you have finished this series, be sure to watch the spin off series, Stargate: Atlantis. It is a worthy successor to this brilliant series.
EDIT on 7-18-08: I just found out that Don S. Davis died a few weeks ago. It is a great loss.