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_name
from q111 -- wrong queue ,finger trouble.
In theLog file viewer :
The activated proc [dbo].[HandleRecieveMsg] running on queue EOGDB.dbo.q2eog output the following: 'Invalid object name 'q111'.'
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.
|||If you are doing that within a transaction scope, the transaction should rollback and the message should be put back into the queue.