what should i choose? LOL
OK, cut the nonsense and get to some links right?
Unfortunately, I won't say buy this one!!!! But can give my recommendation as the choices I'd consider.
Are you in the USA? If so I'd say your money is best spent on a:
$200 X800 pro
or
$200 6800GS
If you have more money and want a TV tuner, remote, video editing features and some more performance:
$285 AIW X800XT
If you have money, Want to stay with AGP, and don't have use for the AIW features, go with the most reviewed overclocked 7800GS,
BFG 7800GS $313 or $10 less for a reference clocked version. I'm with those who feel that this card and the X850XT are not worth $300+. But they should give you a decent Overclock for a little more performance.
Or find a deal on a X850XT, which I can't right now. :x
Of course going with a new PCI-e mobo and video card like Cleeve said and you can get alot more performance than any available AGP card will give you, in COD2 and basically everything else. Sure it depends on your cpu and budget, but true kick butt gaming in new titles like COD2 (to me) = go A64 and a higher end PCI-e card. [H]
seems to agree with their 7800GS right up. Or check Anandtech or Toms for a more in depth 7800GS review. Or Firingsquad for COD2 benchies:
http://www.firingsquad.com/hardware/evga_e-geforce_7800_gs_co_superclock_review/page12.asp
Lastly, no matter which of the above AGP card you choose, make sure your power supply is up to the task. If you have less than a name brand 350W PS with 18amp on the 12v rail (read power supply label inside your case), your will probably need a new one or suffer stability issues. The exact amount of power needed depends how loaded down you computer is with drives, cards, fans, etc. But a generic 300-350W with 14A or 15A on the 12 volt rail probably will not cut it for these cards, especially if you have multiple optical drives and/or HDD's.
Let us know what you decide. Any one of these cards mentioned would be a big jump up from your FX5900XT and should make you happy as long as you know not to expect high res and eye candy in COD2.