Explicit Gamer has profiled five "great" video games that ended up being the final nail in the coffin for the studios that developed them. Troika's Vampire: The Masquerade - Bloodlines is an obvious mention, though I'm not sure why they refer to White Wolf's tabletop system as a "card game":
Using the underused and superb Vampire: The Masquerade card game license, they aimed to do Deus Ex with vampires. Bloodlines was a first-person RPG with shooting, sneaking and persuasion elements mixed in with horror, blood-sucking and big-breasted women with secrets to hide (and not many clothes to hide it in). The game also contained the single best haunted house recreation in gaming.
However awesome that sounds, there were two snags however. The first is that the game was using Valve's Source engine, and would become the only non-Valve game to do that. Half-Life 2 hadn't even been released, and Valve insisted that Bloodlines not be out first. So it was released on the same day as Half-Life 2, the most anticipated PC game of all time.
The second problem was that it was utterly unfinished, and despite being an excellent and unique game that is still one of the few games that attempted to "do" Deus Ex, it was lambasted as being unplayable. These two combined sealed the fate of both Bloodlines and Troika. Fan patches are still being released for the game now, illustrating just how much of a following the game has.
It should be noted that both major ex-Black Isle developers, Troika and Obsidian, are regularly criticized for releasing unfinished games. Let's hope that Troika's fate doesn't befall Obsidian too.