The same setup worked fine last summer before I went away to school. All I can think of that's different now is that I upgraded from 10.5 to 10.6 while I was at school -- could this have something to do with it?
Also, if I can't get this to work, how do other people send video signal from their MacBook to a TV with composite video input?
I've struggled with this same problem before, but I had access to a MiniDV camcorder with FireWire / DV input and AV output pass-through capability. In my setup, I used the FireWire port on my MacBook Pro to display video on an analogue TV. I know that others have had success with VGA adapters, but it seems to be hit-and-miss. I suggest you try to return your product if it's still under warranty, as it does not operate as advertised (if it does in fact say Mac compatible on the box).
Alternatively, you could try using a DV converter box if you wish to go the FireWire route (as opposed to finding a MiniDV camcorder), but the cost is going to be roughly the same as with a VGA adapter.