Merged images look disappointing at first. That's because screens can't accurately represent the 32 bits/channel range without further processing. Think of the merged image as film negative. Photomatix calls this process Tone Mapping.
Tone mapping reveals the details in highlights and shadows contained in the original HDR image. It converts the HDR image in 32 bits/channel mode into an image in 16 or 8 bits/channel mode that can be saved as a JPEG.
How to do it yourself?
Using a tripod for the captures is recommended to prevent undesired shaky look. One thing to keep in mind is that if the original photos have a tiny bit of noise, when you layer them, you will get 3 times as much noise. I learned this the hard way. Try to snap the pictures at low ISO speeds to prevent noise.
Once you have captured your RAW photos you have to merge them. Photoshop CS4 has an automated script which allows you to do the merge. I find photoshop's HDR capabilities very limited. A better option is Photomatix. For about $99 you get a very nice MAC or PC application that allows you to merge you photos into HDR magic.
I am still playing with the this style of photo composition and don't really have any of my own photography I am proud to show, very soon though.
If you want to get your hands wet with HDR I would recommend downloading a trial of the Photomatix software (all features are enabled, but you will get a watermarked final image). You can use pretty much any camera that shoots RAW and allows for exposure bracketing. A tripod or steady surface is required to achieve best results, especially for night shots where longer exposure is required.
Start off by setting the exposure bracketing setting to +/-1 and adjust from there. I find the best results to start showing up at around +/-2.