Service Broker: add message to queue

In this post let’s dwell on adding messages to queues. In our system we prefer doing it inside triggers. Why triggers? There are several reasons:

  • You cannot miss trigger event, whenever you perform update through web-service, or direct in the database.
  • Trigger is always inside your transaction.
  • Also you can get data from the record state before update. This can be very useful.

On the other hand, triggers are not really good way to store any logic, but if it contains only adding messages to queue, this may be rather good option. In any way, we’ve decided to use triggers.

I will describe AFTER UPDATE trigger, as AFTER INSERT will be the same, but simpler as only one record will be proceeded through AFTER INSERT trigger.

— Head of the script will make it executable whether it exists in the database or not.
SET NOEXEC OFF                              
GO
IF OBJECT_ID(‘<TriggerName>’) IS NOT NULL
BEGIN
       SET NOEXEC ON
END   
GO
— Creating empty object
CREATE TRIGGER dbo.<TriggerName>
on dbo.<TableName>
AFTER UPDATE
AS
RAISERROR (‘Object is empty. Error!’, — Message text.
               16, — Severity.
               1 — State.
               );
GO
SET NOEXEC OFF
GO
ALTER TRIGGER dbo.<TriggerName>
ON dbo.<TableName>
after  UPDATE
AS
       DECLARE @conversationHandle UNIQUEIDENTIFIER
 
— Declare cursor for adding messages
— as while multi row update
— table inserted will have data for all updated records,
— and trigger will be executed at the end of batch update
— not after update of each record
 
       DECLARE cur CURSOR
       READ_ONLY LOCAL FORWARD_ONLY
       FOR SELECT
             inserted.id,
— some additional logic
             case
                    when (inserted.IsDMark > deleted.IsDMark) then 3
                    when (inserted.IsDMark < deleted.IsDMark) then 4
                    when (inserted.IsDMark = deleted.IsDMark) then 1
             end as actionid
       FROM inserted
       left join deleted on inserted.id = deleted.id
       — some conditions
       where
       (inserted.IsPosted > deleted.IsPosted)
       and inserted.IsReturned=0
 
    declare @documentID int
    declare @actionid int
    declare @xml xml
 
 
       OPEN cur
 
       FETCH NEXT FROM cur INTO @documentID, @actionid
       WHILE (@@fetch_status = 0)
       BEGIN
       — open the dialog
             BEGIN DIALOG CONVERSATION @conversationHandle
                    FROM SERVICE [//TMS/ServiceBroker/WarehouseManagementProcessing/Init]
                    TO SERVICE ‘//TMS/ServiceBroker/WarehouseManagementProcessing/Target’
                    ON CONTRACT [//TMS/ServiceBroker/WarehouseManagementProcessing]
                    WITH ENCRYPTION = OFF;
 
       — create xml for Service Broker message
                    set @xml = (select
                                  @documentID as ‘ObjectID’
                             , @actionID as ‘ActionID’
                             , getdate() as ‘VerDateTime’
                    for xml path(”), type, root (‘root’));
 
       — send message
                    SEND ON CONVERSATION @conversationHandle
                           MESSAGE TYPE [//TMS/ServiceBroker/WarehouseManagementProcessing/StandardMessage]
                           (@XML)
 
       — close dialog
             END CONVERSATION @conversationHandle
 
             FETCH NEXT FROM cur INTO @documentID, @actionid
       END
 
       CLOSE cur
       DEALLOCATE cur

 

After trigger execution the queue will have one or several messages waiting for other service to process them. Now you can view not processed messages in the queue:

select * from dbo.<Queue name>

 

If you want to clear the queue, you have to execute the following script:

declare @conversation uniqueidentifier
while exists (select * from <Queue name> )
begin
      set @conversation = (select top 1 conversation_handle from <Queue name> )
      end conversation @conversation with cleanup
end

 

Also, it is important to say, that when you deactivate the queue, the messages will still be received but not processed, and when you activate the queue they all will appear in the queue.

Advertisements
This entry was posted in Database development, SQL Server, Technology and tagged , , , . Bookmark the permalink.

2 Responses to Service Broker: add message to queue

  1. Your style is so unique in comparison to other folks I have read stuff from.
    Thanks for posting when you have the opportunity,
    Guess I will just book mark this site.

    • Boris Frolov says:

      Thanks a lot for your comment. In April I have changed job and my location – no free time for posts. Planning to come back to my blogs in September 2013.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s