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  )