I presume this OP was in part brought about by my bad-mouthing of rockets in another thread.
A rocket is the only way to go in this situation, as a cannon has to be really big to handle the recoil involved. The rocket is bigger than the cannon shell + propellant charge but is still much smaller than the cannon shell + charge + honking huge cannon.
On the other hand, as other posters have pointed out, in real life situations of firing a rope, people almost always prefer to use a cannon. Why is that?
With a cannon, you shoot it, and the shell goes where you aim it. Sure win, humidity, barrel wear and your zodiac sign makes a difference, but only at long ranges.
Now, with a rocket, a lot goes on. Consider a straight one-stage rocket. You shoot it, it comes out of the tube still on fire, it sets your hair on fire, and then some neat stuff happens. The rocket is still being propelled downrange. It hits the wind and the pointy end points upwind, encouraged perhaps by the rocket’s fins. This means the fiery end is pointing downwind. So you can see, the rocket tends to fly upwind.
Now as a practical matter, nobody like having their hair on fire. (Richard Prior: “People get out of your way.”) So we will use the Acme Two-Stage Rescue Rocket. Fire it, a small booster rocket kicks the thing out of the tube and burns out in the tube. leaving your hair intact. The second stage ignites at some safe distance, and the rocket proceeds upwind as before.
Consider what that means. It goes out of the tube, it falls down (as it is unpowered), it ignites the second stage, it flies upwind. As I said, sort of a loopty-loop sort of action as you see on the RPG family (well the early ones like the RGP-7 family.) Great fun to fire.
OK, so what else can we do with a rocket? We use a one-stage rocket that burns out inside the tube. All the bazooka sort of things you see in movies work like this. A single, very strong rocket kick the thing all the way to the target, but burns out before it leaves the tube, sparing your hair.
Since it is moving very fast, it does not drop much before getting where it is going. Since it is unpowered, it does not fly upwind. Problem? All the propulsion has to be applied in the short length of the launch tube. The rocket is in fact quite like a recoilless rifle. It can carry any amount of weight you like as long as you make the rocket much more powerful that the motors used in the types I described above.
Anything else? It is near bedtime here.