0

I followed this tutorial: https://gallery.technet.microsoft.com/scriptcenter/Using-Service-Broker-for-360c961a

and it is working for me, However, I don't understand some thing: At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE (1=1). Why ? After all, during creating queue we bind messages with this procedure:PROCEDURE_NAME = proc_BrokerTargetActivProc.

In addition, I am not sure If I correctly understand way of working it: ExecuteProcedureAsync push to queue message with name of procedure to execute. What now ? How does it work that BrokerTargetActivProc will be called with exactly one message ?

What about parameter MAX_QUEUE_READERS = 5 ?

Thank in advance, Regards

Improve this question

1 Answer 1

Reset to default
0

At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE (1=1). Why ?

The process for receiving messages in SQL Server requires what I call a semiactive polling. The code makes a request to the queue for the next message, then waits for a response. That is what the WAITFOR block is doing.

WAITFOR 
( RECEIVE TOP(1) 
    @RecvReqDlgHandle = conversation_handle, 
    @RecvReqMsg = message_body, 
    @RecvReqMsgName = message_type_name 
  FROM TASK_QUEUE 
), TIMEOUT 5000;

If the process was not in an infinite loop, the procedure would poll once, either receive a message or timeout, then quit. With the infinite loop, the procedure polls continuously until stopped.

ExecuteProcedureAsync push to queue message with name of procedure to execute. What now ? How does it work that BrokerTargetActivProc will be called with exactly one message ?

When ExecuteProcedureAsync is called, it places the message in an asynchronous message queue. This is a process that is external to SQL Server, accepts messages from sending processes, and forwards them to the receiving process when it is ready for the next message (that is, when the receiver polls the queue for the next message on the queue). The reference to BrokerTargetActiveProc when putting the message on the queue is letting the queue know where the message is going. It is not an interrupt that triggers the BrokerTargetActiveProc procedure.

In an asynchronous queue like this, if the receiver is down, then the messages simply wait in the queue until the receiver comes back up or the queue runs out of resources to hold messages.

Improve this answer
9
  • 1.Could you explain me, what are services here ? In reference to queue nad procedures. 2. Who execute proc_BrokerTargetActivProc procedure ? (which in real is infinite loop getting one message from queue). 3. What does it mean these parameters?: @RecvReqDlgHandle = conversation_handle, ` @RecvReqMsg = message_body, ` ` @RecvReqMsgName `
    – user114705
    Commented Jan 10, 2017 at 18:32
  • 1
    1) The Service Broker is the service used to manage the queues in this case. Other situations might use RabbitMQ or another external message queue handler. 2) In general: proc_BrokerTargetActivProc will either be activated by a SQL Server job, a startup procedure, or on the start of an application that needs the proc to handle messages. The exact startup mechanism depends on what you need and when you need it.
    – Laughing Vergil
    Commented Jan 10, 2017 at 18:41
  • 1
    3) [a] conversation_handle is a Service Broker UniqueIdentifier for the message queue, saved in the variable @RecvReqDlgHandle. [b] message_bodyis the actual message that was placed on the queue, saved in @RecvReqMsg for processing after it is pulled off of the queue. [c] message_type_name lets the receiving process understand what type of message was sent. As an example, if a message queue was set up to handle all inserts and updates to table Customers, the receiver needs to know if a given message was an Insert message or an Update message. This would be where that info was passed.
    – Laughing Vergil
    Commented Jan 10, 2017 at 18:47
  • When it comes to above example (link) ? Who call proc_BrokerTargetActivProc ?
    – user114705
    Commented Jan 10, 2017 at 18:48
  • Is it difficult to call procedure with additional parameter?
    – user114705
    Commented Jan 10, 2017 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.