The most demanding games out there that i can think of would be F.E.A.R, Battlefield 2, and Call of Duty 2 is also pretty demanding. With a 6800GS, you should be able to max out those games, or come very close to it.
I'm still saying that with the release of the 6800GS, it's the best bang/buck card now. It'll hugely outperform the 6600GT, be more future proof, and isn't that much more.
The video card doesn't do THAT much for the BF2. The CPU makes a pretty huge difference in BF2. Also, a 3200+ at stock outperforms a P4 XE 3.73GHz at stock in BF2
Get a 6800GS. People were dumb to recommend the 6600gt even before the release of the 6800gs, because the 6800 vanilla was only $20 more and outpreformed it by a fair ammount.