A question on Conversation timer persistence

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

I'm not sure I understand the requirements. Why is that you need to fire a timer as a result of a field change? The usual requirement is to fire a message so that some asynch processing happens later, but not based on a timer. Can you give some more details?

You can have only one timer per conversation. That what the BOL line refers to. You cannot have multiple timers, setting a new timer will erase the old one.

The timers are set as absolute time, not interval. After a database/server restart, if the time of the timer is in the past, then the timer will be fired.

HTH,
~ Remus

|||

Remus,

Thanks for the quick response.

Here's the workflow of the process: A user creates a work order for which they can assign a follow-up time. When this follow-up time arrives, I want to send a message to a Service Broker queue that I have already set up to process the message. If the follow-up time changes, the timer is adjusted to account for the change in time.

The only difference between what I need to do now and what I've already done is sending the message to the queue at a specific time. I imagined a trigger that would fire every time the follow-up time changed so that I could alter the conversation timer. This trigger would begin a new conversation, set a timer, and an activated stored procedure would look for the message type http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer and send a message to my original queue where it will be processed. I see 2 problems with my logic: I am looking for a DialogTimer message type, so the activated stored procedure only knows it is time to do something, but it has no message body that I can use to forward onto the final Service Broker queue. Also, I have no way of finding the conversation I started the last time the trigger fired.

I'm wondering if using a conversation timer is the wrong approach, and if so, what is?

Thanks,

Chris

|||

What you need is a table to associate the conversation which fired the timer with the original work order. When the work order is created, the trigger begin a dialog, sets the timer and then inserts into this table the newly created conversation handle and the work order id.

When the timer fires, the activated procedure receives the message, looks up the work order id in this table (based on the conversation handle the message was RECEIVE on) and does whatever work is required at that moment.

The same table can be also used when updates occur on the follow_up field. Instead of beginning a conversation, the trigger will look up this association table and find the existing conversation.

One thing to note is that timer messages are unlike any message in the sense that they are sent by one conversation endpoint to itself. So the conversation handle on which the timer was set is the same one as on which is going to be received.

I do believe that conversation timers are the right approach. No other approach I can think of is better. Conversation timers are very cheap from a resource point of view, completely contained within the database (this gives lots of advantages related to backup/restore, failover and availability), and offer the possibility to actually luch a procedure.

HTH,
~ Remus

|||

Thanks a lot Remus. A state table was what I came up with as well. I really appreciate being able to come here for valuable, practical advice on how to approach Service Broker issues. Thanks again,

Chris