First Contact works provided that you accept certain contrivances. One of those is that the sphere is rather weak. You could say perhaps that the power required to generate the chronoton vortex + the battle weakened the sphere's reserves.
(I can't believe I just came up with another Treknology justification
Another thing that follows is:
"Sphere's shields/defenses = pathetic." A torpedo spread from the E-E destroyed it cleanly. Very different from the spheres encountered in VOY. That said, maybe the Queen deliberately allowed the sphere to be destroyed.
And to go off the weird conspiracy angle, perhaps she wanted the assimilation of both Earth and the E-E to get the best of both technologies.
However, the other contrivance is the whole idea of time travel...if this were so great a gambit, a cube could have time traveled in the Beta Quadrant and come over to Earth or something.
Oh well, it's still cool. Kinda like how James Bond dodges all those bullets, it so happened that the sphere stank when it was supposed to. But the Borg were still quite formidable in the film.