<p>In what domains do each of these software architectures shine or fail?</p>

<p>Which key requirements would prompt you to choose one over the other?</p>

<p>Please assume that you have developers available who can do good object oriented code as well as good database development.</p>

<p>Also, please avoid holy wars :) all three technologies have pros and cons, I'm interested in where is most appropriate to use which.</p>

I agree that there are pros and cons to everything and a lot depends on your architecture. That being said, I try to use ORM's where it makes sense. A lot of the functionality is already there and usually they help prevent SQL Injection (plus it helps avoid re-inventing the wheel).
Please see these other two posts on the topic (dynamic SQL vs 
stored procedures vs ORM) for more information
Dynamic SQL vs. stored procedures
Which is better: Ad hoc queries, or stored procedures?
ORMs vs. stored procedures
Why is parameterized SQL generated by NHibernate just as fast as a stored procedure?