Good day,
I have send a msg successful,but the proc failed. I altered the proc to correct the problem in the proc. The msg are still in the queue. Is there a manual way to excute the proc again to process the msg in the q? or do I wait for service broker to do it after a retry time (self recover)?
thank you in advance.
If the proc fails (i.e. throws an exception), Service Broker will try to restart the proc every 5 seconds. However, if your proc actually did a RECEIVE which was rolled back, then the rollback handler will detect this as a poisoned message and disable the queue. You can check this by doing:
select
is_receive_enabled from sys.service_queues where name='YourQueue';You can enable the queue by:alter
queue [YourQueue] with status = on;Rudhi
what I did :
receive
top(1) @receivedMsg = message_body,@h
= conversation_handle,@msgType
= message_type_namefrom q111 -- wrong queue ,finger trouble.In theLog file viewer :
So I corrected the proc to point to the correct queue.
I did as you said: "select is_receive_enabled from sys.service_queues where name='q2eog'; " q return a value of 1.
The message still is in the correct q after 5 seconds and longer.
Is there something else I can look or do?
Okay I followed the steps in the help:
"Symptom: Activation Stored Procedures Do Not Run". And discovered that the proc I execute inside the RecieveProc fails.
(the proc I exec inside the recieving proc is proc reference a external assmbly)
Now my q is if a proc fails and the msg is allready retrieved from the queue, what happens to the msg? where does it go or the error msg?
You can look at the state of the queue monitor:
select * from sys.dm_broker_queue_monitors;
If it is in 'NOTIFIED' state (which means, waiting for someone to do a RECEIVE), you can either reset the state by doing a RECEIVE or an ALTER QUEUE:
ALTER QUEUE [YourQueue] WITH STATUS = ON;
Hope that helps.
