Can't enable queue

Hi Folks,

I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:

ALTER QUEUE MigrationQueue WITH STATUS = ON;

ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);

I would have thought the first line would've worked but I get the following when trying to receive...

The service queue "MigrationQueue" is currently disabled.

Help.

[918 byte] By [robmiller214] at [2007-12-24]

«« Rendering to Form's PictureBox (for Beta 1)
»» Security Permissions

# 1

Check the is_receive_enabled column in sys.service_queues to make sure it really was enabled. If not, how did you get rid of the poison messages? Maybe there's still one left around.

Roger_Wolter_MS at 2007-10-8 >

top of Msdn Tech,SQL Server,SQL Service Broker...

# 2

Do you not have to end the conversations that have the poison messages.

SimonS at 2007-10-8 >

# 3

yep. i looked in sys.service_queues and is_receive_enabled was 0. i found out that i needed to end the conversations, so i used the conversation id and bled the queue. with nothing in the queue, i reran alter queue <myqueue> status = on; still nothing. i'm about to drop and readd the queue... argh.

robmiller214 at 2007-10-8 >

# 4

Is activation enabled on the queue? Could it be that you enable it, activation kicks in and rollback again 5 times, disabling it back right away.

HTH,
~ Remus

RemusRusanu at 2007-10-8 >

# 5

That was it! Maybe I should remove the RAISERROR I was using to test my error handling. Doh!

robmiller214 at 2007-10-8 >

SQL Server

SQL Service Broker