Error Handling in Activation Procs

I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.

I tried using @.@.Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

Hmmm...interesting. Where did you read the best practice about not rolling back the part of your code that receives the record off of the queue. I am not sure that makes sense to me. I would want to put the message back onto the queue if I know I have ran into an error.|||The idea is to avoid Poison Messaging where possible by accounting for whatever errors you know you will encounter (foreign key errors, Invalid Data types)...these types of errors will never be able to run and will therefore, bring down your queue every time if you rollback. The idea is to never roll back the receive, unless there is some type of fatal error that causes the batch to abort. In that case, as long as your receive is in a transaction, it will be rolled back automatically. If it is a handled error, you need to do something else with that message to move it off the queue.|||

On one of our systems we use seperate transactions for recieving the message and processing the message. The transaction around the receive simply removes the message from the queue and logs it to an audit table. If this step fails then we rollback and put the message back onto the queue. If we successfully receive and log the message we then begin another transaction (if applicable) for the processing of the message. Therefore if this fails for some reason (ie constraints) then we just rollback the processing and not the receive from the queue - we can then get the message from the audit table to re-process if required. Our idea is to keep the code in the transaction that includes the receive from the queue as simple as possible to avoid failures and prevent rollbacks - therefore avoiding poison messages. I'm not sure if this would be applicable in your case.