This the best DSLR I've owned. It has so many more plus points than minus, I'll concentrate to begin with on what I think might be considered negatives:
Cons:
(1) "Only" 12 million pixels.
If this is a genuine issue for you, go no further, there's nothing you can do about it - at this price or less, at time of writing, your Nikon option is the 16MPix DX sensored D7000. In full-frame, it's presently just the Canon Eos 5D or 5D MKII (both excellent cameras, 21MPix), or hold out for a '700' version of the Nikon D3X (24.5MPix).
My personal experience is that for the kind of shooting I tend to do, in low light, more pixels are usually wasted, noisy pixels. If you light your subjects or generally shoot short exposures in bright light, _need_ an awful lot of pixels per square millimetre, and have lenses which can do them justice, you might well want to look elsewhere.
(2) Weight.
This isn't an especially small camera. It's a lot lighter and smaller than its enormous top-end stablemates, but it's still a fair chunk of your carry-on baggage allowance and can be a literal pain in the neck after an extended period carting it about. Especially if you have, say, an 80-200 f/2.8 attached to it. At around a kilogram, it's over twice the weight of a Canon EOS 500D, 100g more than an EOS 5D, but quarter of a kilo lighter than a Nikon D3 or Canon EOS 1Ds.
(3) Full frame.
Generally, full frame is a positive thing, but there are circumstances in which the smaller DX frame is advantageous: Lighter, cheaper lenses; perhaps better suited for long-range wildlife photography, etc. With good lighting, a 50mm f/1.8 or f/1.4 makes an extremely affordable and superb portrait lens on a DX crop sensor. 80mm f/1.4 remains expensive, and 200mm f/2.8 is an awful lot cheaper than 300mm f/2.8!
(4) A little noisy
This camera makes quite a clunk when it goes off. Partly because of the fast frame rate and large mirror, I guess. With the MD10 battery grip, shooting at 8fps, this sounds like a mini machine-gun. You wouldn't want to use this to photograph classical soloists in concert, or on live TV sets or whatever, at least not without a blimp, you'd get thrown out.
Pros - pretty much everything, in my opinion:
Everything else I have to say about this camera is good. I absolutely love shooting with this camera, and the results are almost always better than I feel I have any right to expect, encouraging me to develop and improve my photography.
My first DSLR was a Nikon D70, which I bought around 2004. A few years after that I bought a Pentax K10D (I have both Nikon and Pentax lenses) which cost less but outshone the D70 in every way except flash sync speed (the D70's 1/500sec remains unsurpassed, even in Nikon's line-up). I've used a D200, and found it superb. I looked at the D700 when it came out, wanted it the moment I picked one up in a shop, but couldn't afford it. Circumstances change, though, and I eventually bought one, with an MB-10 grip bundled on offer from Nikon.
When I had the D70, I would take 35mm SLRs with me as well - I loved the instant feedback of digital, but the camera's squinty little viewfinder was a pain to use, the DX crop was inconvenient for wideangle. The 35mm cameras were a pleasure to use by comparison and the results with Velvia or Provia, albeit expensive, easily blew away the quality possible with the D70.
With the Pentax, I started to set aside 35mm - the 10Mpixel results were very good, colour rendition much better than the D70, the viewfinder among the best available in DX sensor cameras at the time, the controls quick and intuitive. I'd still carry a 35mm body from time to time, but found myself using film less and less.
First impressions
My first impression of the D700 was that DSLRs had finally made it back to the "real camera" feel of the best 35mm SLRs. I have a couple of Nikon FM2s and a Pentax LX - the D700 was the first DSLR I tried which offered a similar viewfinder and similarly un-obtrusive controls. It is as satisfying and straightforward as any 35mm SLR I've used, and it gives me unprecedented confidence in getting the picture I was trying for. Low-light performance exceeds anything 35mm film can do, and each of the 12Mpixels seems to count in ways they don't on the more densely packed sensors.
Uses
This isn't a studio camera, at heart. Although relatively heavy, it's not actually all that much heavier than the K10D, and it's happiest out and about - rock gigs, street photography, reportage are where it shines. Quick to use, quick shooting, beautiful viewfinder, virtually unparalleled low-light performance, you can get pictures which simply aren't possible any other way. With the K10D and its built in image stabilizer, I got used to shooting at silly low shutter speeds handheld, with a variable hit rate. With the D700, the weight of the camera/grip combo keeps things pretty steady without the need for a stabilizer, and for the first time I can push the ISO beyond 800 and still get pictures worth looking at.
Compatibility
I've a huge penchant for solidly built old manual-focus prime lenses, and with these this camera excels. Like the D200, D300 and other Nikon pro/semi-pro cameras, the D700 works very well with old lenses. It has the lens throat ring which picks up the aperture ring setting from AI lenses, and (obviously) with more recent CPU lenses it offers all the modern conveniences. There are very few Nikon fit lenses made in the last 30+ years which won't work with the D700. About the only exceptions are a few extremely exotic ones which project too far back into the mirror box to use without a full-time mirror lock-up.
For manual focus, the viewfinder is clear, adequately dioptre-adjustable and accurate - I feel confident manually focusing fast lenses (e.g. a 50mm at f/1.4) using the D700, where with the K10D I often found I'd be out if shooting wide open (someone suggested that this was a common issue with modern auto-focus cameras because of the limited effective aperture of the viewing system itself).
Features
I'm not going to go into detail about the D700's many impressive and useful features, many of which I haven't properly explored or have little use for - this information is widely available online. I'll skim the ones which matter to me: The interval timer can be handy, the many picture controls and "active D-lighting" etc. provide all the flexibility you could hope for, the continuous shooting buffer is deep enough that I've never once noticed it slowing me down shooting RAW files. I've tried the 3D tracking autofocus, which really does work, and really is impressive, although as I said I'm more of a manual focus kind of person. The live-view mode is probably the only feature I've tried which I've found wanting - it seems unduly awkward to use, and of course the viewing screen is fixed so this isn't as much use for e.g. macro photography as it might be.
Another noteworthy aspect of the D700 seems to be an apparent reduction in chromatic aberration at the frame edges with certain wide-angle lenses. I don't know whether it's a feature of the sensor itself (different microlens design, sensor elements closer to the surface?), or some digital cunningness, but it's a welcome quality.
Conclusion
In general, control-wise, and for overall feel, I prefer the D700 to the Canon EOS 5D (I haven't tried a 5D MKII). The D700 seems better made than the Canon, and the controls suit me better. On the other hand it's more expensive and has fewer pixels.
No amount of enthusiastic rambling will tell you whether to buy this camera, but I would certainly strongly encourage you to pick one up and try it out if you're considering cameras in this price/performance bracket.