Comparing the X1950Pro and the 8800GT isn't really fair as they are completely different generations of cards, however I will say the following.
With the 8800GT things feel far "brighter", colours are more vibrant and image quality is far higher. Gauges are more readable with the eyepoint zoomed back on the 8800GT. Self shadowing and bloom hit the 8800GT far higher than the X1950Pro, in fact I've left both off permanently and can't see ever turning them back on with this card which suffers a huge 30fps plus drop with either enabled, whilst the ATI card only saw a few fps knocked off the total. The ATI card didn't see much of a performance hit when using AA and AF either, whilst the Nvidia card takes a slightly higher hit. The 8800 mip-maps the ground textures far better and gives a much better illusion of distance. I also don't see the horrible "blocking" blurries that I had with both of my previous ATI cards (X1950Pro and 9800XT). Instead the ground textures in the distance are, of course, blurred, but they fade in much more naturally. (The same cannot be said of autogen, but unfortunately that is an MS fault and has nothing to do with hardware.)
To be completely honest after a few FSX.cfg tweaks and setting everything up on the new card properly it's come down to the fact that I can now fly with maximum autogen and enjoy it, whilst for high, smooth performance the X1950 required autogen to be either off or at it's minimum (I preferred off, I found it easier to envision the ground textures as buildings than ground textures with one building stuck in the middle). That said I am impressed by how clear and crisp and bright and vibrant the Nvidia card is compared to the ATI card, it makes things feel much more immersive. Now I can fly with trees and buildings enabled all of a sudden my enjoyment of the sim has lept up and I'm finding it very very hard to get back to work.
Incidentally regarding the reason for the upgrade:
Sapphire X1950Pro - idle: 45 deg C, load: 115+ deg C and lots of crashes and artifacts due to Sapphire being utterly utterly useless and fitting duff coolers.
MSI 8800GT "Zilent" - idle: 35-40deg C, load: 45-50 deg C and is completely silent.
To conclude, if you're thinking about upgrading to the 8800GT I'd definately recommend the MSI version with the Zalman cooler, sure its an extra £30 compared to the standard MSI 512mb 8800GT (£170+ as opposed to £145) but the cooler would set you back that if you fitted it yourself and the chips seem to be very high quality and I'm 100% sure you can squeeze a lot more oopmh out of the card in the future when it starts to lag a bit.
http://www.komplett.co.uk/k/ki.aspx?sku=343946
If you're still sitting on the fence about picking up an 8800GT go and do it, right this instant!

The lesson learned from this? Just make sure you avoid Sapphire! (Another manufacturer who won't be getting my hard earned cash in future). They've fixed the cooler problems now but if a manufacturer pushes a card out that has major cooling problems they clearly don't test things correctly before releasing them and from my own point of view I'd avoid them like the plague. FSX is now 100% my de-facto standard sim, I used to use it a good 90% of the time but the lack of autogen annoyed me to no end. My system is by no means incredibly powerful nor expensive (2GB PC5300, 2.4Ghz core 2 duo, MSI P965 Platinum, 8800GT) and I'm getting perfect performance now in the sim with SP2/Acceleration installed.
Apologies for the rather lengthy tome but I'm sure someone will find it useful.