Hi Bubblehead,
I answered a couple of similar threads like this one recently.
To get the full potential from such an upgrade you will need 2 x 512mb PC3200 DDR RAM modules running in dual channel mode on the 400mhz bus speed that an AMD XP3200+ runs at.
The RAM needs to run "in sync" at the same speed as the front side bus to obtain maximum system bandwidth.
The added processing power of the 3200+ will be substantially complimented by the switch from your present 333mhz (ddr) Front Side Bus speed to the new FSB speed of 400mhz.
The 2600+ cpu you have is an older generation CPU which has an unlocked multiplier if I'm correct. (model 8 )
That cpu had limited overclock potential due to heat issues arising from the manufacturing process. It is also difficult to successfully raise the front side bus speed very high on the 2600+.
Later cpu's used a different process which helped the heat issues and most of these cpu's had their multipliers locked internally (model 10). With no mutliplier to wind up, the FSB speed needs to be wound up for an overclock.
Currently I'm running a model 10 XP2800+ with the FSB speed at 365mhz (default 333mhz), syncronised and dual channel ram.
This little bit of extra speed on the FSB really gives a nice little boost, and my system benchmarks are as good or better than a 3200+ equipped rig, for reasons I'm not really qualified to answer.

I had always planned on making the move to a 3200+ myself, and, I have PC3500 RAM installed already to make it easy for me to overclock a new 3200+ and see if I can really get this old board flying!
The potential is certainly there to make a kickbutt 32bit system.................however.......... I now have to consider purchasing a socket 939 CPU (with associated mainboard) instead, as the socket 939 component prices are dropping rapidly. Remember, I already have the correct ram, even for the 64bit system.
The switch to a socket 939 64bit platform should prove to be much larger performance increase than the 3200+ cpu upgrade.
If you need to buy new PC3200 ram, consider only the added cost of a slightly more expensive 64bit cpu, and a socket 939 mainboard to move into a 64bit platform.
The nforce2 ultra 400 chipset lives on, but only just!
Choices!
This shows how my CPU is overclocked on the Front Side Bus, giving a substantial boost to CPU power from it's stock speed. If a CPU is not good enough to overclock in this way, it will become apparent through faulty operation (instability) and possible overheating.

This shows system memory bandwidth, which is really a PC's potential throughput power. This benchmark is actual bandwidth and any system bottlenecks will show up in this test. Memory bandwidth is influenced by chipset, FSB speed and RAM speed / timings.