It's an enjoyable franchise for the most part, I've got the first two shows on DVD.
It's not a great show though and just about reaches okay status.
Strangely I give this show more leeway then Voyager, which in my opinion is of the same low writing quality. Certainly when watching both they feel a part of a tired franchise that needs a rest. Difference being the Stargate franchise felt like that from the outset.
The main cast was nearly always bland and uninteresting. The only character I grew attached to was Rodney McKay, which is strange since I normally can't stand his type of character.