Lesson 2: Creating an Internal Activation Procedure
In this lesson, you will learn to create a stored procedure to process messages from a Service Broker queue. You will also learn how to specify that the procedure be activated any time there are messages in the queue.
Switch to the AdventureWorks2008R2 database
Copy and paste the following code into a Query Editor window. Then, run it to switch context to the AdventureWorks2008R2 database.
USE AdventureWorks2008R2; GO
Create an internal activation stored procedure
Copy and paste the following code into a Query Editor window. Then, run it to create a stored procedure. When it is run, the stored procedure keeps receiving messages as long as there are messages in the queue. If the receive times out without returning a message, the stored procedure ends. If the received message was a request message, the stored procedure returns a reply message. If the received message is an EndDialog message, the stored procedure ends the target side of the conversation. If the received message is and Error message, it rolls back the transaction.
CREATE PROCEDURE TargetActivProc AS DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER; DECLARE @RecvReqMsg NVARCHAR(100); DECLARE @RecvReqMsgName sysname; WHILE (1=1) BEGIN BEGIN TRANSACTION; WAITFOR ( RECEIVE TOP(1) @RecvReqDlgHandle = conversation_handle, @RecvReqMsg = message_body, @RecvReqMsgName = message_type_name FROM TargetQueueIntAct ), TIMEOUT 5000; IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END IF @RecvReqMsgName = N'//AWDB/InternalAct/RequestMessage' BEGIN DECLARE @ReplyMsg NVARCHAR(100); SELECT @ReplyMsg = N'<ReplyMsg>Message for Initiator service.</ReplyMsg>'; SEND ON CONVERSATION @RecvReqDlgHandle MESSAGE TYPE [//AWDB/InternalAct/ReplyMessage] (@ReplyMsg); END ELSE IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN END CONVERSATION @RecvReqDlgHandle; END ELSE IF @RecvReqMsgName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN END CONVERSATION @RecvReqDlgHandle; END COMMIT TRANSACTION; END GO
Alter the target queue to specify internal activation
Copy and paste the following code into a Query Editor window. Then, run it to specify that Service Broker activate the TargetActiveProc stored procedure to process messages from TargetQueueIntAct. Service Broker will run a copy of TargetActiveProc any time a message is received in TargetQueueIntAct and no copy of the procedure is already running. Service Broker will run additional copies of TargetActiveProc whenever the existing copies do not keep up with the number of incoming messages.
ALTER QUEUE TargetQueueIntAct WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = TargetActivProc, MAX_QUEUE_READERS = 10, EXECUTE AS SELF ); GO
You have successfully configured AdventureWorks2008R2 to support a conversation between the //AWDB/InternalAct/InitiatorService and the //AWDB/InternalAct/TargetService. Next, you will complete a conversation using the configuration. See Lesson 3: Beginning a Conversation and Transmitting Messages.