How to set is_receive_enabled and is_enqueue_enabled from 1 to 0.
-
27 ธันวาคม 2549 18:12
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
ตอบทั้งหมด
-
27 ธันวาคม 2549 20:29
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
-
29 ธันวาคม 2549 15:57
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
-
29 ธันวาคม 2549 22:55
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
-
4 มกราคม 2550 15:19
yeah.. that's should my probme. Thanks a lot.
-
3 มกราคม 2555 13:25thank u guys.. the postings helped me a lot and saved my time
Thanks, Satish Kumar. -
12 เมษายน 2556 13:32
Hey,
I am also facing the same issue....what was your solution to the problem.
after executing ALTER QUEUE [ExternalMailQueue] WITH STATUS = ON is_receive_enabled and is_enqueue_enabled is again and again getting 0.
Please let me know the workaround for the same and what was your next step after this.
Regards, Ashish
- แก้ไขโดย Ashish_DbaSQL 12 เมษายน 2556 13:33
-
12 เมษายน 2556 13:34
thank u guys.. the postings helped me a lot and saved my time
Thanks, Satish Kumar.Please let me know the workaround for the same and what was your next step after this.
Regards, Ashish
- แก้ไขโดย Ashish_DbaSQL 12 เมษายน 2556 13:34
.png)
