What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application? I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it.

the system will have a asp.net UI, web services middle tier and SQL2005 back end. The UI and Services will communicate using JSON calls.

I was wondering on how to ensure transactions are committed and if not for any error to bubble up and be logged. ideally the action to be retried a couple times after 5 minute intervals, like an email app does.

I was planing to use try catch blocks in SQL and was wondering what the interface (or contract if you will) would look like between the SQL stored procs and the Services that call them would look/ function. this interface will play 2 roles one is to pass params for the proc to function and return expected results. the next will be for the proc to return error information. maybe somethign like error number and error message.

my quagmire is how to i structure this intelligently so that the services expect and react accordingly to both data and error info returned from procs and handle each accordingly?

is there a framework for this because it seems very boiler plate?
Of course! Like most interesting problems in mission-critical applications, this was actually solved by IBM in the 1970s, but the IT industry is always chasing the shiniest new thing and has no respect for the great engineers of the past. I am referring of course to message oriented middleware, or as it was known back in the day, TPF. The principle of this is actually very simple, and has been proven time and time again, by banks, airlines, telcos, you name it.

The fundamental operations are, to place a message on the queue, and to take a message off the queue. So when your application needs to do some work, it packages that as a message and places it on the queue for the next service. The TPM then dequeues that message and attempts to get work done on it. If this fails, the message simply goes back onto the queue to be tried again, and if it continues to fail, gets shunted across to the queue that handles failures. The message bus also handles routing, load balancing and all that good stuff. You can buy these capabilities off the shelf: MQSeries and Tibco Rendezvous are the "serious" ones, but there are plenty of others around, many of which are compatible with JMS so you aren't tied into one vendor (of course if you go JMS then you are also bound to the lowest common denominator). You spec your message bus to be ultra-reliable and fault-tolerant, then you just hang the services off the back of it, and put your app on the front. SOA is another example of an old idea that's been reheated with new buzzwords...