Well, just to add my penny's worth... Here we use Castle ActiveRecord (which is actually a wrapper around NHibernate, it just allows declarative syntax rather than having to write XML files).
Personally I really like from a developer's point of view, but it doesn't support SPs which makes it harder to customise things and also leads to unnecessary data being loaded, etc... As a result of this I have now built a simple ORM which supports stored procedures, and gives me the best of both worlds.
As has been mentioned already, I do think ORM tools have a place in development, but they are not a fix-all solution - I know of people who use them because they can't write TSQL at all!!!!
IMHO there is no substitute for a well-written SP layer which then goes through to a lovely set of entity classes... the best of both worlds!!
I have some code I could pass on, PM me if you want to take a look (Warning - It's very simple, with no relationship support but it does what I want it to do!)