Using Service Broker to Communicate With Other Database Servers

The other parameter, MAX_QUEUE_READERS, specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time. The value of max_readers must be a number between 0 and 32,767.

We create the SERVICE by combining the QUEUE and CONTRACT.

CREATE SERVICE svrStockUpd ON QUEUE queSendStockSend ([MainContract])

Now we have to write a sp to input data into the queue. This sp will have two parameters: itemcode and qty. inside the sp, XMLmessage will be formatted and sent to the queue.

CREATE PROCEDURE [dbo].[usp_StockInfo]
 @ItemCode [varchar](15),
 @Qty [int]
AS
 BEGIN
  DECLARE @OrdDate AS SMALLDATETIME
  SET @Orddate = GETDATE() -- We assume current date and time as the order date
  DECLARE @Message XML
  CREATE TABLE #XMLMessage
   (
    [ItemCode] VARCHAR(15),
    [Qty] INT,
    [OrderDate] SMALLDATETIME,
   )

  INSERT INTO #XMLMessage
    (
     [ItemCode],
     [Qty],
     [OrderDate]
    )
  VALUES (
     @ItemCode,
     @Qty,
     @ORddate
    )

  SELECT @Message = ( SELECT * FROM #XMLMessage
       FOR XML PATH('Order'),
         TYPE
       ) ;
  -- Above will fomulate valid XML message
  DECLARE @Handle UNIQUEIDENTIFIER ;

  -- Dialog Conversation starts here

  BEGIN DIALOG CONVERSATION @Handle FROM SERVICE svrStockUpd TO
    SERVICE 'svrStockUpd' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ;

  SEND ON CONVERSATION @Handle MESSAGE TYPE SendStockDetails (@Message) ;
 END
GO

You can observe that the service is used for DIALOG CONVERSATION.

The message will be in the following XML format.

<Order>
 <ItemCode>2001</ItemCode>
 <Qty>60</Qty>
 <OrderDate>2007-02-21T00:32:00</OrderDate>
</Order>

First let's input some date by using usp_stockinfo.

[usp_StockInfo] 'A-200',12

You will notice that you will be returned immediately after the above executes.

If you execute SELECT * FROM dbo.queSendStockSend you can view the message data. However, you will see that message body is encrypted. (Download the code in the example above.)

Next we should create a sp to read the queue. In practice, this should be created before the queue is created, as you need to specify the activation stored procedure while creating the queue.

CREATE PROCEDURE usp_updatetocks
AS
 BEGIN

  SET NOCOUNT ON ;
  DECLARE @Handle UNIQUEIDENTIFIER ;
  DECLARE @MessageType SYSNAME ;
  DECLARE @Message XML
  DECLARE @OrdDate SMALLDATETIME
  DECLARE @Qty INT
  DECLARE @ItemCode VARCHAR(15) ;

  RECEIVE TOP ( 1 )
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body FROM dbo.queSendStockSend;

  IF ( @Handle IS NOT NULL
    AND @Message IS NOT NULL
   )
   BEGIN
    SELECT @OrdDate = CAST(CAST(@Message.query('/Order/OrdDate/text()') AS NVARCHAR(MAX)) AS SMALLDATETIME)
    SELECT @Qty = CAST(CAST(@Message.query('/Order/Qty/text()') AS NVARCHAR(MAX)) AS INT)
    SELECT @ItemCode = CAST(CAST(@Message.query('/Order/ItemCode/text()') AS NVARCHAR(MAX)) AS VARCHAR(15))

    INSERT INTO dbo.tblOrders
      (
       ItemCode,
       Qty,
       OrderDate
      )
    VALUES (
       @ItemCode,
       @Qty,
       @OrdDate
      ) ;
   END
 END

To read the queue, we have used the RECEIVE command. With the RECEIVE command you are deleting the queue record after receiving it. If you use SELECT, it will not be removed from the queue. As the message is in a binary format, we need to convert it using the CAST function. We will convert them to three separate variables and insert them into the table.

After this, if you run select * from tblOrders, you can see that data has been logged in the tblorder table by Service Broker.