5.如何自動從 TargetQueue 接收對話
對於此演示,我們將使用在本文件的另一部分中建立的服務代理構造。提到的部分稱為 3.在資料庫上建立基本服務代理構建(單個資料庫通訊) 。
首先,我們需要建立一個能夠從 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
第二步:允許 TargetQueue 自動執行你的程式:
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 )