How to set is_receive_enabled and is_enqueue_enabled from 1 to 0.

Hi All,

I used service broker activation in my receive queue (SQL 2005 SP1). However, it seems the stored procedure does not work after the receive queue get message. I saw the following error in SQL Error log.

"The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'"

Then I checked the queue in sys.service_queues and noticed is_receive_enabled and is_enqueue_enabled is 0 but not 1 (is_activation_enabled is 1, which is normal). I believe this is the cause of my service broker activation issue.

According to this link:

http://www.eggheadcafe.com/aspnet_answers/SQLServerservicebroker/May2006/post26788966.asp

I tried to use ALTER QUEUE ... WITH STATUS = ON command to set the above value back to 1, it does not work the value persists 0. Any idea?

Thanks in advance.

Michael

ALTER QUEUE [YourQueue] WITH STATUS = ON will enable the queue and both is_receive_enabled and is_enqueue_enabled will be set to 1. However, if your stored proc rolls back a RECEIVE 5 times consecutively, the poisoned message detector will automatically disable the queue. You should see error messages in the ERRORLOG if your stored proc is throwing an exception or not committing the RECEIVE correctly.

Rushi

|||

Thanks, Rushi. You are correct the receive queue is disabled and I also found 5 errors before the queue is disabled in the SQL ERRORLOG as below:

--

2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.95 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.96 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.98 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:48.01 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:50.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:55.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:00.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:05.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:08.09 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'

Can you let me know:

a. Is the 5 times a built-in value or the value I can set it? Is it the option MAX_QUEUE_READERS = 5 while I set the activation?

b. Since the ReceiveQ is disabled, how can I enable it back? I tried "ALTER QUEUE [YourQueue] WITH STATUS = ON" before, but the ReceiveQ still is disabled. Is it normal? Can I set it back?

Michael

|||

a. The maximum number of times RECEIVE can be consecutively rolled-back without setting of the poisoned message detection cannot be configured. It is hard-coded to '5'. It is also not related to the MAX_QUEUE_READERS which controls max number of concurrent activated tasks.

b. The ATLER QUEUE should re-enable your queue. Of course, if there are pending messages it will also start activation and since you have a bug in your stored proc, the stored proc will rollback 5 times and disable it back.

Rushi

|||

yeah.. that's should my probme. Thanks a lot.