use [broker]
go

alter database [broker] set enable_broker with rollback immediate;
GO

CREATE TABLE log_line (
    log_line_id INT identity(1, 1) NOT NULL,
    log_line_ts TIMESTAMP,
    device_id varchar(128) not null,
    application_name VARCHAR(64) NOT NULL,
    application_user_name VARCHAR(6) NOT NULL DEFAULT '',
    log_line_dt DATETIME NOT NULL,
    log_line_message VARCHAR(max) CONSTRAINT pk_log_line_id PRIMARY KEY (log_line_id)
    )
GO


CREATE MESSAGE TYPE [mtInsertLogLine] VALIDATION = WELL_FORMED_XML
go

CREATE CONTRACT [cInsertLogLine]
(
    [mtInsertLogLine] SENT BY ANY
)
go

CREATE QUEUE qReceiveLogLine
    WITH RETENTION = ON, --can decrease performance
        STATUS = ON;
GO

CREATE QUEUE qInsertLogLine
    WITH RETENTION = ON; --can decrease performance
GO

CREATE PROCEDURE spInsertLogLine
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Handle UNIQUEIDENTIFIER;
    DECLARE @MessageType SYSNAME;
    DECLARE @Message XML;
    DECLARE @device_id VARCHAR(128);
    DECLARE @application_name VARCHAR(64);
    DECLARE @application_user_name VARCHAR(6);
    DECLARE @log_line_dt DATETIME;
    DECLARE @log_line_message VARCHAR(max);

    RECEIVE TOP (1) @Handle = conversation_handle,
        @MessageType = message_type_name,
        @Message = message_body
    FROM [qInsertLogLine]

    IF (
            @Handle IS NOT NULL
            AND @Message IS NOT NULL
            )
    BEGIN
        SELECT @device_id = CAST(@Message.query('/log_line/device_id/text()') AS NVARCHAR(MAX))

        SELECT @application_name = CAST(@Message.query('/log_line/application_name/text()') AS NVARCHAR(MAX))

        SELECT @application_user_name = CAST(@Message.query('/log_line/application_user_name/text()') AS NVARCHAR(MAX))

        SELECT @log_line_dt = CAST(CAST(@Message.query('/log_line/log_line_dt/text()') AS NVARCHAR(MAX)) AS DATETIME)

        SELECT @log_line_message = CAST(@Message.query('/log_line/log_line_message/text()') AS NVARCHAR(MAX))

        INSERT INTO log_line (
          device_id,
            application_name,
            application_user_name,
            log_line_dt,
            log_line_message
            )
        VALUES (
          @device_id,
            @application_name,
            @application_user_name,
            @log_line_dt,
            @log_line_message
            );
    END
END
GO

ALTER QUEUE qInsertLogLine WITH ACTIVATION
 (
      STATUS = ON,
      MAX_QUEUE_READERS = 5, --number of concurrent instances of spInsertLogLine
      PROCEDURE_NAME = spInsertLogLine,
      EXECUTE AS OWNER
 );
 GO

CREATE SERVICE sReceiveLogLine ON QUEUE qReceiveLogLine([cInsertLogLine])
go

CREATE SERVICE sInsertLogLine ON QUEUE qInsertLogLine([cInsertLogLine])
go

create PROCEDURE spSendLogLine
(
    @device_id varchar(128),
    @application_name VARCHAR(64),
    @application_user_name VARCHAR(6),
    @log_line_dt DATETIME,
    @log_line_message VARCHAR(max)
)
AS
BEGIN

      DECLARE @MessageBody XML
      CREATE TABLE #ProcParams
      (
        device_id varchar(128),
                application_name VARCHAR(64),
                application_user_name VARCHAR(6),
                log_line_dt DATETIME,
                log_line_message VARCHAR(max)
      )
      INSERT INTO #ProcParams (device_id,application_name,application_user_name,log_line_dt,log_line_message)
      VALUES(@device_id, @application_name, @application_user_name, @log_line_dt, @log_line_message)

      SELECT @MessageBody = (SELECT * FROM #ProcParams FOR XML PATH ('log_line'), TYPE);

      DECLARE @Handle UNIQUEIDENTIFIER;

      BEGIN DIALOG CONVERSATION @Handle
            FROM SERVICE [sReceiveLogLine]
            TO SERVICE '[sInsertLogLine]'
            ON CONTRACT [cInsertLogLine]
            WITH ENCRYPTION = off;

            SEND ON CONVERSATION @handle
                     MESSAGE TYPE [mtInsertLogLine]
                     ( @MessageBody );

END
GO