Microsoft SQL Server
Agente de servicios

1. Fundamentos

Service Broker es una tecnología basada en la comunicación asíncrona entre dos (o más) entidades. Service Broker consta de: tipos de mensajes, contratos, colas, servicios, rutas y al menos puntos finales de instancia

Más: https://msdn.microsoft.com/en-us/library/bb522893.aspx

2. Habilitar Service Broker en la base de datos

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

3. Crear la construcción del agente de servicios básicos en la base de datos (comunicación de base de datos única)

USE [MyDatabase]  

CREATE MESSAGE TYPE [//initiator] VALIDATION = WELL_FORMED_XML;
    GO

CREATE CONTRACT [//call/contract]
(
    [//initiator] SENT BY INITIATOR 
)
GO

CREATE QUEUE  InitiatorQueue;
GO

CREATE QUEUE  TargetQueue;
GO

CREATE SERVICE InitiatorService
      ON QUEUE InitiatorQueue
(
     [//call/contract]    
      
)

CREATE SERVICE TargetService
ON QUEUE TargetQueue 
(
     [//call/contract]    
      
)

GRANT SEND ON SERVICE::[InitiatorService] TO PUBLIC
GO

GRANT SEND ON SERVICE::[TargetService] TO PUBLIC
GO

No necesitamos ruta para una comunicación de base de datos.

4. Cómo enviar comunicación básica a través de Service Broker.

Para esta demostración usaremos la construcción del agente de servicios creada en otra parte de esta documentación. La parte mencionada se llama 3. Cree una construcción básica de Service Broker en la base de datos (comunicación de base de datos única) .

USE [MyDatabase]

DECLARE @ch uniqueidentifier = NEWID()
DECLARE @msg XML 

BEGIN DIALOG CONVERSATION @ch
    FROM SERVICE [InitiatorService]
    TO SERVICE 'TargetService'
    ON CONTRACT [//call/contract]
    WITH ENCRYPTION = OFF; -- more possible options

        SET @msg = (
                    SELECT 'HelloThere' "elementNum1"
                    FOR XML PATH(''), ROOT('ExampleRoot'), ELEMENTS XSINIL, TYPE
                    );         
      
SEND ON CONVERSATION @ch MESSAGE TYPE [//initiator] (@msg);
END CONVERSATION @ch;

Después de esta conversación será tu msg en TargetQueue

5. Cómo recibir la conversación de TargetQueue automáticamente

Para esta demostración usaremos la construcción del agente de servicios creada en otra parte de esta documentación. La parte mencionada se llama 3. Crear la construcción básica de Service Broker en la base de datos (comunicación de base de datos única) .

Primero necesitamos crear un procedimiento que sea capaz de leer y procesar datos de la cola.

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

Segundo paso: permita que su TargetQueue ejecute automáticamente su procedimiento:

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  )