Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Service Broker / May 2006

Tip: Looking for answers? Try searching our database.

Activation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Richard - 17 May 2006 20:40 GMT
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
Remus Rusanu [MSFT] - 17 May 2006 21:13 GMT
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).

Signature

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

>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
[quoted text clipped - 3 lines]
> Thanks,
> Richard
Richard - 17 May 2006 21:58 GMT
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?

> ALTER QUEUE ... WITH ACTIVATION (STATUS = ON)
> will start activation. Make sure you don't use
[quoted text clipped - 9 lines]
>> Thanks,
>> Richard
Remus Rusanu [MSFT] - 17 May 2006 23:06 GMT
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.

Signature

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

> That worked.  Thanks.
>
[quoted text clipped - 18 lines]
>>> Thanks,
>>> Richard
Richard - 18 May 2006 02:05 GMT
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?

> ALTER QUEUE ... WITH STATUS  = ON/OFF controls the is_receive_enabled and
> is_enqueue_enabled columns.
[quoted text clipped - 32 lines]
>>>> Thanks,
>>>> Richard
Remus Rusanu [MSFT] - 18 May 2006 04:09 GMT
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?

Signature

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

> That kind of describes the issue.  Here's a little more detail on what
> happens.
[quoted text clipped - 48 lines]
>>>>> Thanks,
>>>>> Richard
Richard - 18 May 2006 22:18 GMT
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?

> 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
[quoted text clipped - 52 lines]
>>>>>> Thanks,
>>>>>> Richard
Remus Rusanu [MSFT] - 18 May 2006 22:32 GMT
NEVER use END CONVERSATION WITH CLEANUP in a program.
See http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

Signature

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

> The rows in the transmission queue are processed again, but they are never
> removed from the transmission queue and are processed over and over.
[quoted text clipped - 62 lines]
>>>>>>> Thanks,
>>>>>>> Richard
Richard - 19 May 2006 03:21 GMT
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

> NEVER use END CONVERSATION WITH CLEANUP in a program.
> See http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx
[quoted text clipped - 65 lines]
>>>>>>>> Thanks,
>>>>>>>> Richard
Roger Wolter[MSFT] - 19 May 2006 05:12 GMT
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

Signature

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

> Thanks for your all of your help.
>
[quoted text clipped - 107 lines]
>>>>>>>>> Thanks,
>>>>>>>>> Richard
Remus Rusanu [MSFT] - 19 May 2006 19:18 GMT
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]

Signature

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

> Thanks for your all of your help.
>
[quoted text clipped - 107 lines]
>>>>>>>>> Thanks,
>>>>>>>>> Richard
Richard - 19 May 2006 21:15 GMT
That was the problem.  Thanks!

> You must check the message type received. Any application should be
> prepared to deal with the system message types (EndDialog, Error and
[quoted text clipped - 123 lines]
>>>>>>>>>> Thanks,
>>>>>>>>>> Richard
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.