I'm looking at implementing Service Broker to resolve some performance issues in one of our applications where certain long running processes hold up the front end UI for users.
So far, I've successfully set up the steps in Service Broker for the message types, queues etc and that's working fine. What I want to do now though is in the message reply at the end, call another stored procedure which will take up to a minute or two to run, but without that impacting the return of the message reply and ending the conversation. I've added a segment of code below to show how I'm trying to do the final reply and end the conversation.
-- Receive reply and end conversation
DECLARE @ReceivedReplyMessage NVARCHAR(100);
DECLARE @ReceivedReplyHandle UNIQUEIDENTIFIER;
( RECEIVE TOP(1)
@ReceivedReplyHandle = conversation_handle,
@ReceivedReplyMessage = message_body
), TIMEOUT 1000;
-- T-SQL statement for running SP
-- end T-SQL
END CONVERSATION @ReceivedReplyHandle;
SELECT @ReceivedReplyMessage AS ReceivedReplyMessage;
So basically the application calls an SP which generates the initiator message and sends it off. Service Broker than accepts that, sends an acknowledgement back, calls the external stored procedure and then ends the conversation, so that the front end application can continue to work as soon as it gets the acknowledgement/reply while the database runs the Stored Procedure.
Does that make sense, or have I missed something obvious?