The Centauri of Babylon 5 in general (and Londo in particular) seem at first to be too backstabby to be much of a threat. Of course, then, Londo gets in bed with Morden, and the Centauri start the Second Shadow War; but then, when Londo goes back to Centauri Prime, it proves that the Centauri have been sad little pawns the whole time.
Blackadder: Prince Blackadder the First was such a foolish loser that he was mostly an object of pity for the audience, despite his scheming ways. It didn't help that much of the time he was more of a victim than an aggressor. His descendants were also often victims but as far as competence was concerned, they were very much not worthy of pity (the fourth one would have no problem of getting it however).
Harmony from Buffy the Vampire Slayer, after she became a vampire. She's evil, but other than that, she's still that teenage girl who wants to have friends and be loved. She is willing to kill her friends and allies, but at the very same time, she sincerely likes them and does not want them to be angry at her.
That said, the fact that she murders people to drink their blood (she is a vampire, after all) makes her somewhat less sympathetic.
This finally starts working for her in Angel, where she ends up being promoted out of the typing pool to Angel's secretary because she's a friendly face, admittedly one last seen trying to feed them to a group of vampires. Even when she betrays them again, Angel doesn't hold it against her, and actually prepared a reference for her so she can get a new job.
The Trio also start out this way. They quickly become less sympathetic — though not less ineffectual, however, as even Warren murdering Tara was an accident. He meant to kill Buffy.
The only exception is Jonathan, who remains sympathetic, and actually becomes less evil as the other two get worse - he even helps Buffy defeat Warren.
The Stillman Sisters from an episode of Charmed, entitled "The Power of Three Blondes". They are trying to steal the Halliwell sisters' powers and prove that they're more than just dumb blondes, and they come oh-so-close to succeeding at both.
The Master on Doctor Who, especially in the classic era. He rants about how superior he is and how great it is that he will finally defeat his hated frenemy the Doctor, but most of the time his Evil Plan backfires and the Doctor ends up saving him from himself.
Game of Thrones: Lancel Lannister is perhaps the dimmest of the Lannisters, and despite his Jerkass tendencies, it's hard not to feel sorry for him considering how Robert treats him... and then how Cersei treat him. Hell, how everyone treats him.
Col. Klink in Hogan's Heroes, to the extent that he and Col. Hogan clearly become close friends as the series progresses. (Hogan and his men did their best to make sure Klink stayed in charge, because if he were ever transferred, someone worse would have taken over.)
Governor Croque in Jack-of-All-Trades has a role similar to Klink's in Hogan's Heroes. He's not all that bad a guy, but more to the point, any likely replacement would either be more intelligent (and thus more likely to figure out what Jack and Emilia are up to), more tyrannical (and thus more likely to inflict suffering on the innocent people of Pulau Pulau), or both.
Poor dumb Dewey Crowe of Justified, who probably only survived as a criminal for as long as he did because no one could be bothered to take him seriously.
Robbie Rotten in LazyTown. His schemes never work, and the fact that his nemesis is an Invincible Hero doesn't help.
Peter Campbell of Mad Men. Sure, he's an obsequious little jerk who is looking for any opportunity to take advantage of any tiny opening. He's a total jerk to ANY and ALL women and he's so passive aggressive that it's sickening. And yet he's almost sympathetic because he's a constant failure with puppy dog eyes. Then Character Development kicks in, by Season 4 he's possibly the single most sympathetic (in the sense that "you actually like him because he's a good guy") character on the show.
On M*A*S*H, there was "Five o'Clock Charlie", an incompetent North Korean bomber (or maybe just a farmer who had fixed up a plane) who was trying to bomb the 4077, always coming at 5PM on the dot (something only an idiot would do) and proving unable to hit the side of a barn. Most of the cast didn't take him seriously and saw him as entertainment. He might have been a Harmless Villain had not Frank Burns overreacted and almost caused a crisis, which could have resulted in him being shot down (and act that would likely have brought real attacks on the unit).
Dr. Clayton Forrester of MST3K springs to mind. As his character brief in the show's official Episode Guide puts it, "His passion for depravity far exceeds his aptitude." Then came The Castle of Fu Manchu...
El Eco-Loco in Odisea Burbujas, a garbage-theme villain with terrible silly plans, he even end crying at some episodes.
Crowley in Supernatural doesn't always fit this trope, but he's had his moments. He seems to flip-flop between wanting to be a badass demon overlord and sincerely trying to be a lesser evil, and his success either way is somewhat mixed.
After his first (unsuccessful) effort at double-crossing Lucifer in Season 5, the next time he meets the Winchesters he's on the lam, protesting to the boys "They killed my cat! They ATE MY TAILOR!" Of course at this point he's also on their bad side, as his bad information got Jo and Ellen killed.
After he succeeded in becoming King of Hell in Season 6, when summoned by Bobby to address the matter of the latter's soul, he popped a couple of Alka-Selzer in lieu of his usual glass of Scotch, complaining about the difficulties of trying to better the lot of his hopelessly corrupt demon brethren.
And of course in Season 7 he winds up living in hiding in a rundown house trailer after being ironically double-crossed by Castiel, who takes on the monster souls they had been jointly trying to acquire and briefly assumes the role of God.
Crossing the border to Real Life here. Smoking Gun: World's Dumbest Criminals features quite a few of these. From the guy who broke in a convenience store through the roof, but couldn't get back out, to the group of guys who broke into a department store and stole all the display models (which have no working components).
Special mention goes to a pair of teens who walk into a police station and try to hold it up. This robbery attempt goes about as well as one would expect.
Chett in Weird Science apart from being Big Brother Bully after the first episode all of his mean actions end always backfiring due to a combination of Lisa's magic and his own stupidity turning into a permanent Asshole Victim in every episode.
Wiseguy. Mark Volchek runs the town of Lynchboro, Seattle as a personal fiefdom. The OCB is sent in to investigate him, only to find that his big plan is merely to build a cryogenic storage hospital for the entire town in order to sate his own personal phobia of death.
Dracula in Young Dracula is nicknamed "The Draculoser", and in one episode it's implied that he's taken to subsisting on animal blood, not for moral reasons, but because he's just too pathetic to hunt humans successfully.