ASP.NET, SQL Server, WPF, C#, VB.NET

I need to temporarily stop an activated queue (alter with status = off).  
  
The queue continues to accept rows, but when I start the queue again (alter  
  
with status = on), the queue entries don't get processed.  How do I resume  
  
normal operations so that the queued entries get processed?  
  
Thanks,  
  
Richard

ALTER QUEUE ... WITH ACTIVATION (STATUS = ON)  
  
will start activation. Make sure you don't use  
  
ALTER QUEUE ... WITH STATUS = ON  
  
which is different (it enables the queue, not the activation ont he queue).  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
HTH,  
  
~ Remus Rusanu  
  
SQL Service Broker  
  
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:eqKPBneeGHA.1320@TK2MSFTNGP04.phx.gbl...

That worked.  Thanks.  
  
The reason I ran into that is because when a queue errors 5 times its status  
  
(is_receive_enabled and is_enqueue_enabled) gets set off, and other valid  
  
entries stay in the queue.  When I set the status on (alter queue... with  
  
status on) the remaining valid queue entries don't get processed.  How does  
  
the queue get put back into a state so that valid entries are processed via  
  
activation?  
  
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote  
  
in message news:O9Cjq5eeGHA.1436@TK2MSFTNGP05.phx.gbl...

ALTER QUEUE ... WITH STATUS  = ON/OFF controls the is_receive_enabled and  
  
is_enqueue_enabled columns.  
  
ALTER QUEUE ... WITH ACTIVATION (STATUS = ON/OFF) controls the  
  
is_activation_enabled column.  
  
The activation starts processing messages when all three columns are 1. BTW,  
  
is_receive_enabled and is_enqueue_enabled values are always identical in SQL  
  
2005.  
  
When the queue is disabled because of poison message detection, the  
  
is_receive_enabled/is_enqueue_enabled columns are changed to 0 (the queue is  
  
disabled). The is_activation_enabled should be left at 1. When the  
  
is_receive_enabled/is_enqueue_enabled is changed from 0 to 1 (he queue is  
  
enabled back), the activation should start.  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
HTH,  
  
~ Remus Rusanu  
  
SQL Service Broker  
  
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:eFqKySfeGHA.4304@TK2MSFTNGP05.phx.gbl...

That kind of describes the issue.  Here's a little more detail on what  
  
happens.  
  
* send 2 good transactions, everything is good  
  
* send 5 bad transactions, queue gets disabled  
  
* send 3 more good transactions, they stay in the transmission queue and I  
  
captured the error "The service queue "MyQueue" is currently disabled."  
  
* execute the command "alter queue... with status on"  
  
* send 2 more good transactions, they process normally  
  
* the 3 good transactions that were enqueued when the queue status was off  
  
are still in the transmission queue  
  
How can I programmatically process the 3 rows that are seemingly stuck in  
  
the transmission queue?  
  
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote  
  
in message news:%23ytib4feGHA.5016@TK2MSFTNGP04.phx.gbl...

The messages in the transmission queue will be retried after some time (less  
  
than a minute). How long did you wait for the messages to be enqueue and  
  
processed?  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
HTH,  
  
~ Remus Rusanu  
  
SQL Service Broker  
  
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:OPeKacheGHA.1792@TK2MSFTNGP03.phx.gbl...

The rows in the transmission queue are processed again, but they are never  
  
removed from the transmission queue and are processed over and over.  
  
"Processing" includes writing the message_body to another table.  The last  
  
code to execute is "end conversation with cleanup."  I don't get any client  
  
or logged errors, and this is all within a transaction that's within a  
  
try/catch -- the catch logs errors to another table.  
  
Any ideas?  
  
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote  
  
in message news:Op2fJiieGHA.1456@TK2MSFTNGP04.phx.gbl...

NEVER use END CONVERSATION WITH CLEANUP in a program.  
  
See http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
HTH,  
  
~ Remus Rusanu  
  
SQL Service Broker  
  
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:eoQBXCseGHA.3588@TK2MSFTNGP02.phx.gbl...

Thanks for your all of your help.  
  
When a row is first queued, the row is successfully processed, but then it  
  
seems to attempt to be processed 5 more times until the queue is disabled.  
  
In the following snippet, the raiserror causes an exception five times after  
  
the first successful process.  This does not occur when "with cleanup" is  
  
part of the "end conversation" command.  I know I'm doing something wrong,  
  
but can't figure out what.  
  
begin try;  
  
begin transaction;  
  
waitfor  
  
(  
  
receive  
  
top( 1 )  
  
@ConversationHandle   = conversation_handle,  
  
@QueueMessage = message_body  
  
from  
  
q_Request  
  
), timeout 12000  
  
if @@rowcount !> 0  
  
begin  
  
rollback  
  
return @ERROR_NOTHINGQUEUED  
  
end  
  
if @QueueMessage is null  
  
raiserror ( 'null queue message error', 18, 5 )  
  
end conversation @ConversationHandle --with cleanup  
  
commit  
  
end try  
  
begin catch  
  
rollback  
  
exec dbo.p_ExceptionLog  
  
return @ERROR_UNHANDLEDEXCEPTION  
  
end catch  
  
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote  
  
in message news:uSsMRLseGHA.3640@TK2MSFTNGP03.phx.gbl...

The only reason a message would stay on the queue to be reprocessed after  
  
being received would be if the transaction that contains the RECEIVE is  
  
rolled back  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
Use of included script samples are subject to the terms specified at  
  
http://www.microsoft.com/info/cpyright.htm  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:ePK8%23rueGHA.3484@TK2MSFTNGP02.phx.gbl...

You must check the message type received. Any application should be prepared  
  
to deal with the system message types (EndDialog, Error and Timer). Add a  
  
@message_type_name variable (of type sysname) and receive into it the  
  
message_type_name column. Check this message type in your processing.  
  
You are raising error when message body is null. What probably happens is  
  
that you're receiving the EndDialog message (which does have a NULL body)  
  
and you are raising because of it.  
  
When you're using END WITH CLEANUP, the EndDialog message is not sent (hence  
  
the apparent 'good' behavior).  
  
The complete names of the system message types are:  
  
[http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog]  
  
[http://schemas.microsoft.com/SQL/ServiceBroker/Error]  
  
[http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer]  
  
--  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
HTH,  
  
~ Remus Rusanu  
  
SQL Service Broker  
  
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx  
  
"Richard" <napa299@yahoo.com> wrote in message  
  
news:ePK8%23rueGHA.3484@TK2MSFTNGP02.phx.gbl...

That was the problem.  Thanks!  
  
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote  
  
in message news:eVACeC3eGHA.1320@TK2MSFTNGP04.phx.gbl...