Message with the timer

Question

I'm calling stored procedure asynch with timer:

DECLARE @DialogHandle UNIQUEIDENTIFIER, @RequestMessage NVARCHAR(255);
BEGIN dialog CONVERSATION @DialogHandle FROM SERVICE [SAStockIncreaseService] TO SERVICE N'SAStockIncreaseService', 'current database' WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER(@DialogHandle) TIMEOUT = 10;

It works. The procedure defined within 'SAStockIncreaseService' is called after 10 seconds.
There is message in SAStockIncrease queue:

RECEIVE TOP (1) @Handle = conversation_handle, @message=message_body FROM dbo.SAStockIncrease;

This message is empty. Now I would like to add something to this message.
As i understand from documentation, this message is always empty when using timer. It is not possible to put something inside?

If i would like to send some message inside call of my procedure, i must add this 2 lines:

SET @RequestMessage = N'<Request><SA>58</SA></Request>';
SEND ON CONVERSATION  @DialogHandle(@RequestMessage);

This way I would have 2 messages inside dbo.SAStockIncrease queue, but they will have the same group and conversation handle, so I can new that they belongs together.

It looks stupid to me, why not put message into timer call and have only one record(message) in my conversation, but that is how it is, if I understand correctly.

So, when I execute this:

RECEIVE TOP (1) @Handle = conversation_handle, @message=message_body FROM dbo.SAStockIncrease;

I will always get the timer message first. Than I should check if there is another message with select statement:

SELECT @message=CAST(message_body) WHERE conversation_handle=@handle

But what if some other user executes RECEIEVE TOP(1).. at the same time and clear my message from the queue?

What is the right procedure to read all messages with the same handle at the same time?

I guess it is obvious what I would like to do - so, what is the right way?

Thanks,
Simon

Answer 1

But what if some other user executes RECEIEVE TOP(1).. at the same time and clear my message from the queue?

As long as you don't commit the transaction when you receive the timer message you should be fine. Service Broker locks the conversation group, so no other user can get the other message.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Erland,

I'm not 100% if it is true.
If I execute the following code:

DECLARE @DialogHandle UNIQUEIDENTIFIER, @RequestMessage NVARCHAR(255);
BEGIN dialog CONVERSATION @DialogHandle FROM SERVICE [SAStockIncreaseService] TO SERVICE N'SAStockIncreaseService', 'current database' WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER(@DialogHandle) TIMEOUT = 10;
SET @RequestMessage = N'<Request><SA>58</SA></Request>';
SEND ON CONVERSATION  @DialogHandle(@RequestMessage);

Now I have in queue only one message, since timer message will arrive after 10 seconds(and also stored procedure will be called.
The first problem is that if some other person executes this code in meanwhile(before 10 seconds):

;RECEIVE TOP (1) @Handle = conversation_handle, @message=CAST(message_body AS xml) FROM dbo.SAStockIncrease;
end conversation @Handle with CLEANUP

I will lose the message which I have to read inside stored procedure.
Is it possible to add both messages in queue after 10 seconds?

And second: after 10 seconds I have both messages in queue. But if I execute this:

end conversation @Handle with CLEANUP

only one message will gone from queue and other is still there even if both messages have the same conversation group_id.

Everything would be easy if it would be possible to add message together with timer.

So, at the end:
If i call stored procedure asynchronously and send some data(xml, like parameter values) then it is simple. Here is one complete example:
Call SP asynch

Now,I would like totaly the same as in this example, but the SP must be called after some time. So, I need to use the timer. How would you do that? Use timer with some message data.

Answer 3

First of all, let's look at what you should consider and what you should not.

END CONVERSATION WITH CLEANUP is purely administrative command which you use everything has already gone south, or you just want to clean everything up to reinstall or begin from the beginning. This command should never be issued while the application is working.

Furthermore, someone should not be doing RECEIVE just for fun. RECEIVE command should only be issued by the application code written for the purpose.

That said, what I suggested will not work, because while you can first start the conversation timer and then send the message, the message will most likely be delivered directly and consumed by the activation procedure before the timer elapse. And now? It somehow understands that it should wait for the timer? Not very likely.

I assume that your ultimate aim is to start a stored procedure with some delay. I think this could work:

1) You send the data for the stored procedure call to the target. The message also includes the waiting time.

2) The target receives the messages, sees that there is a delay, and stores the information in a permanent table keyed by conversation handle.

3) The target starts a conversation timer.

4) When the target gets the DialogTimer message it picks up the saved data and runs the procedure.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Erland,

using table with handle would indeed work. But it seems so stupid to me, since timer message should have a place to store some info in it. But as it seems there is no other way.

"END CONVERSATION WITH CLEANUP" - every microsoft example has this command, when the message is received(sometimes without cleanup). But since I don't need to have my message any more, it seems appropriate for me to clean it from system tables and all queues. What damage could it be if I have only timer messages?

Can you explain a bit more why not using end conversation command? Would the messages stay forever in system tables? Or you mean only to end conversation without cleanup?

br, Simon

Answer 5

using table with handle would indeed work. But it seems so stupid to me, since timer message should have a place to store some info in it. But as it seems there is no other way.

What can I say but
http://connect.microsoft.com/SqlServer/Feedback

Be sure to explain your actual business, why you want to start procedures with a delay.

Can you explain a bit more why not using end conversation command? Would the messages stay forever in system tables? Or you mean only to end conversation without cleanup?

There is a big difference between END CONVERSATION and END CONVERSATION WITH CLEANUP!

END CONVERSATION should certainly be used, although just like RECEIVE not because you feel like it, but because the conversation is over. Exactly when and how requires a little thinking sometimes.

---

Erland Sommarskog, SQL Server MVP, [e-mail]