Microsoft SQL Server Service broker 5. How to receive conversation from TargetQueue automatically
Example
For this demonstration we will use service broker construction created in another part of this documentation. Mentioned part is called 3. Create basic service broker construction on database (single database communication).
First we need to create a procedure that is able to read and process data from the Queue
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_RecieveMessageFromTargetQueue]
AS
BEGIN
declare
@message_body xml,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier,
@messagetypename nvarchar(256);
WHILE 1=1
BEGIN
BEGIN TRANSACTION
WAITFOR(
RECEIVE TOP(1)
@message_body = CAST(message_body as xml),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle,
@messagetypename = message_type_name
FROM DwhInsertSmsQueue
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
IF (@messagetypename = '//initiator')
BEGIN
IF OBJECT_ID('MyDatabase..MyExampleTableHelloThere') IS NOT NULL
DROP TABLE dbo.MyExampleTableHelloThere
SELECT @message_body.value('(/ExampleRoot/"elementNum1")[1]', 'VARCHAR(50)') AS MyExampleMessage
INTO dbo.MyExampleTableHelloThere
END
IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @conversation_handle;
END
COMMIT TRANSACTION
END
END
Second step: Allow your TargetQueue to automatically run your procedure:
USE [MyDatabase]
ALTER QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION
( STATUS = ON , --activation status
PROCEDURE_NAME = dbo.p_RecieveMessageFromTargetQueue , --procedure name
MAX_QUEUE_READERS = 1 , --number of readers
EXECUTE AS SELF )
