I thought it started off well, but became worse as it went along. The stories were too repetitive and cheesy and the characters actually became less interesting as time wore on. It's my least favorite of the Stargate series, simply because it became very tedious and the choices the writers made were generally cowardly and cheap.
I think it pales in comparison to SG-1, although it, too, did drag quite a bit towards the end.
Your mileage may vary.