How to determine date/time message was placed onto Queue?

I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.

Which time are you after. The time it was put on the initiator queue or the target queue?

You can add the time to the message when you send it.

|||

Ideally, both.

I was looking for something out-of-the-box... not something where I craft my own message. The messages I will be receiving are based on a pre-defined schema and I did not want to go back to the design table with adding new elements. You would think that the "queue" table would have an additional column for "created date/time" basically.

If anything else, I think that should be a consideration for a future enhancement in the next version of Broker.

|||

Please add a suggestion on connect.microsoft.com/sqlserver/feedback.

Make usre you say exactly which date you are after.

|||There would be a performance penalty for storing SENT and ENQUEUED times into the target queue for each message even if the user was not really interested in those statistics. Our current model is that any user-specific data (sent-timestamp, order-number, request-id, message-id, sender-user-name, etc) would be encapsulated into the message_body itself.

How to delay queue processing?

I am looking for some suggestions on how to implement a delay in processing
queue elements. For example, I am using a queue to process requests to call
a web service. If the web service is unable to process a particular request,
I would like that request to be retried 5 minutes from now while continuing
to process other requests that may be in the queue. I suspect I may need
additional queues, such as a delay queue. Any ideas would be appreciated.

TIA -- Keith.Conversation Timers can be used to do something periodically or to do something after a fixed delay. You can either keep the same dialog you received the message came in on, set a timer and then send the message again when the timer message is received or else put the messages on a different queue and then process that queue periodically. The first option will require keeping the message body around somewhere - either by using retension on the queue or by keeping it in a seperate table (maybe indexed by dialogID).

Here's a little sample that uses timere to do something once a minute:

-- Set up a queue for the timer sample

CREATE QUEUE [TimerQueue]

CREATE SERVICE [TimerService] ON QUEUE [TimerQueue]([DEFAULT]) ;

CREATE SERVICE [TimerResponseService] ON QUEUE [TimerQueue];

GO

-- Create the stored procedure to handle the timer messages

CREATE PROCEDURE [dbo].[HandleTimer] AS

DECLARE @.conversationHandle uniqueidentifier

DECLARE @.message_type_name sysname

DECLARE @.dialog uniqueidentifier

BEGIN TRANSACTION

-- Timer messages only happen once a minute so there's no

-- need to receive in a loop.

WAITFOR (

RECEIVE top(1)

@.message_type_name=message_type_name,

@.dialog = conversation_handle

FROM [TimerQueue]

), TIMEOUT 500

IF (@.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer')

BEGIN

-- Start the next cycle

BEGIN CONVERSATION TIMER ( @.dialog ) TIMEOUT = 60;

-- Do Whatever you want to do here

END

COMMIT TRANSACTION

go

ALTER QUEUE [TimerQueue] WITH ACTIVATION (

STATUS = ON, PROCEDURE_NAME = HandleTimer ,

MAX_QUEUE_READERS = 1,EXECUTE AS SELF)

go

DECLARE @.conversationHandle uniqueidentifier

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE [TimerResponseService]

TO SERVICE 'TimerService';

BEGIN CONVERSATION TIMER ( @.ConversationHandle ) TIMEOUT = 60;