How useless can the Mayer-Vietoris sequence be in general?

Given a long exact sequence

$$ \cdots \to C_{i+1} \to A_i \to B_i \to C_i \to A_{i-1} \to \cdots $$

let the map $A_i \to B_i$ be denoted $f_i$. Then you have that $C_i$ is an extension

$$ 0 \to coker(f_i) \to C_i \to ker(f_{i-1}) \to 0$$

so up to that extension problem, the maps $f_i$ always determine the $C_i$ groups. So if you want a situation where the group $C_i$ is ambiguous, you could have $ker(f_{i-1}) = \mathbb Z_2$ and $coker(f_i) = \mathbb Z$, that way $C_i$ could be either $\mathbb Z$ or $\mathbb Z \oplus \mathbb Z_2$.

Regardless, the connecting map $\partial_i : C_i \to A_{i-1}$ is determined by this extension problem, and it's easy enough to cook up examples either-way.

So I'm a little confused as to the nature of your question. I guess what I'm saying is that you are in the typical situation, and Grigory's example is also typical in that it's the inclusion map that makes the differences between his examples.

Regarding how useful/useless the MVS is for a typical problem, it really depends on how easily-expressible your space is as a union of spaces you understand (and their intersections). If your space doesn't fit that profile, you've got potentially a lot of work to do. The Serre Spectral Sequence of a Fibration is in a sense something of a souped-up Mayer-Vietoris sequence, and there are plenty of papers where people are happy just computing the $E_3$-page, or determining which page the SS collapses on, or computing a differential. These extension issues tend to be very thorny and consume much literature.

Knowing only $H(A)$, $H(B)$ and $H(A\cap B)$ is not enough, of course. For example, taking $A=B=S^1\times D^2$ and gluing them by $S^1\times S^1=A\cap B$ one can get either $X_1=S^2\times S^1$ or $X_2=S^3$. This gives two Mayer-Vietoris sequences with identical $H(A)$, $H(B)$ and $H(A\cap B)$ but different H(X).

As for the situation where one also knows inclusion maps, see Ryan Budney's excellent answer.