Activation proc failed

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.