I know I have to build in some ability to deal with a posion message. I was thinking that (sort of mentioned in the article http://msdn2.microsoft.com/en-us/library/ms166137.aspx) I would write a special stored procedure that would handle this situation. I don't know how I could activate this. I don't want to poll of course in a waitfor.
Does anyone have a good idea on this?
When a poison message is detected and a queue is disabled, an event notification is also fired. To subscribe to this event, create an event notification:
CREATE EVENT NOTIFICATION [QueueDisabled]
ON QUEUE [<queue name>]
FOR BROKER_QUEUE_DISABLED
TO SERVICE 'QueueDisabledServiceHandler', 'current database';
You can nou implment the procedure as an activated procedure on the queue servicing the 'QueueDisabledServiceHandler' service. The event message body contains the name and schema of the queue being disabled because of a poison message.
HTH,
~ Remus
