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.
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
), 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 ?
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.