Service Broker implementation between two tables in one database
I have already provided small demonstration of how service broker works at “Small demonstration of Service broker for beginners” along with list of articles related to service broker from the concept of service broker to its implementation and definition of important part of Service Broker so I will not those concept here in this article, If you wish, you can have a look at those articles, here is the list:
- Importance of Service Broker in SQL Server (Click Here)
- Introduction of Service Broker in SQL Server (Click Here)
- Enable Service Broker in SQL Server database (Click Here)
- Small demonstration of Service broker for beginners (Click Here)
- Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
- Find Service Broker Queue Count in SQL Server (Click Here)
- Get Transmission queue row count in Service Broker (Click Here)
- Purge target Transmission queue of Service Broker in SQL Server (Click Here)
- Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
Let us now start implementing Service Broker for two tables in one database.
Let me explain the business logic which will be used in this article.
Sales people in Company “Extreme Trading” (ET) goes to client site and accept the order from client. Sales people may use iPad, iPhone or any other smart phone, tablet, laptop or other portable device to insert order of client.
There is a rule in company that any order shouldn’t have same client id and project id. If same client give order for same project, it should be added in original order. Original order should be edited. It shouldn’t insert new order id for same client, project combination.
Well, we can define composite primary key or put some validation or check constraint etc. but I don’t want to debate on this rule.
If any duplicate order comes, it should be accepted from the sales people but later on it should insert original order id along with new order id in one table so back office team can handle it and sales person could be free immediately without even waiting for validation gets completed.
Demonstrating this exercise, we will need one database along with two sample table before we start implementing actual service broker.
--create database. CREATE DATABASE ExtremeAdviceSBDemo GO --making broker enable for database. ALTER DATABASE ExtremeAdviceSBDemo SET ENABLE_BROKER ALTER DATABASE ExtremeAdviceSBDemo SET TRUSTWORTHY ON; ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBDemo TO [sa]; GO USE ExtremeAdviceSBDemo GO --two sample table for demonstration CREATE TABLE OrderDetail ( OrderID INT IDENTITY(1,1), ClientID VARCHAR(10), ProjectID VARCHAR(50), OrderDate DATETIME DEFAULT GETDATE() ) GO CREATE TABLE OrderHistory ( ID INT IDENTITY(1,1), OrderID INT, DuplicateWith INT, CheckDate DATETIME DEFAULT GETDATE() ) GO
Now, let us start service broker code. I will not explain each block of code here, if you wish, you can refer my earlier articles given in first paragraph here.
--Create Message Type CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML GO --Create Contract CREATE CONTRACT [EXTREME://EX/Contract/Order] ( [EXTREME://EX/Message/Order/Request] SENT BY INITIATOR, [EXTREME://EX/Message/Order/Response] SENT BY TARGET ); GO --Create Initiator queue reader SP CREATE PROC [OrderInitiatorQueueReader] AS SET NOCOUNT ON; DECLARE @XML XML, @MessageBody VARBINARY(MAX), @MessageTypeName SYSNAME, @ConversationHandle UNIQUEIDENTIFIER, @Response XML BEGIN TRY WHILE (1 = 1) BEGIN BEGIN TRANSACTION WAITFOR ( RECEIVE TOP(1) @MessageTypeName = message_type_name, @MessageBody = message_body, @ConversationHandle = [conversation_handle] FROM dbo.OrderInitiatorQueue ), TIMEOUT 60000 IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0) BEGIN ROLLBACK TRANSACTION BREAK END IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN END CONVERSATION @ConversationHandle; END COMMIT TRANSACTION END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() DECLARE @line INT, @proc VARCHAR(MAX) SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE() SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg END CATCH GO --Create Initiator Queue CREATE QUEUE OrderInitiatorQueue WITH STATUS = ON, ACTIVATION( PROCEDURE_NAME = OrderInitiatorQueueReader, MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ); GO -- Create actual business logic SP CREATE PROCEDURE [CheckOrderMessage] ( @data XML ) AS BEGIN TRY DECLARE @MessageType VARCHAR(50) ,@OrderID INT ,@ClientID VARCHAR(10) ,@ProjectID VARCHAR(50) SELECT @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'), @OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'), @ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'), @ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)') SELECT @MessageType = ISNULL(@MessageType,'') IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN DECLARE @OrderIDOld INT ,@ClientIDOld VARCHAR(10) ,@ProjectIDOld VARCHAR(50) SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID FROM OrderDetail WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID IF(@OrderIDOld IS NOT NULL) BEGIN INSERT INTO OrderHistory (OrderID,DuplicateWith) SELECT @OrderIDOld,@OrderID END RETURN END END TRY BEGIN CATCH DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() END CATCH RETURN GO --Create Target queue reader SP CREATE PROC [OrderTargetQueueReader] AS SET NOCOUNT ON; DECLARE @XML XML, @MessageBody VARBINARY(MAX), @MessageTypeName SYSNAME, @ConversationHandle UNIQUEIDENTIFIER, @Response XML BEGIN TRY WHILE (1 = 1) BEGIN WAITFOR ( RECEIVE TOP(1) @MessageTypeName = message_type_name, @MessageBody = message_body, @ConversationHandle = [conversation_handle] FROM dbo.OrderTargetQueue ), TIMEOUT 60000 IF (@@ROWCOUNT = 0) BEGIN BREAK END SET @XML = CAST(@MessageBody AS XML); IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request' BEGIN --PRINT 'executing CheckOrderMessage @XML' EXECUTE CheckOrderMessage @XML --PRINT 'sending response' SELECT @xml = 'OK'; SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml); --PRINT 'ending cnversation' END CONVERSATION @ConversationHandle; END END END TRY BEGIN CATCH DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() DECLARE @line INT, @proc VARCHAR(MAX) SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE() SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg SELECT @msg END CATCH GO --Create Target Queue CREATE QUEUE OrderTargetQueue WITH STATUS = ON, ACTIVATION( PROCEDURE_NAME = OrderTargetQueueReader, --You can change queue reader number based on your queue load MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ); GO --------------Create SP which will be called from app --or trigger or by any other way --and activate service broker CREATE PROCEDURE [SendOrderRequestMessage] ( @data AS XML ) AS SET XACT_ABORT OFF; BEGIN TRY BEGIN TRANSACTION DECLARE @DialogHandle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @DialogHandle FROM SERVICE [EXTREME://EX/Service/Order/Initiator] TO SERVICE N'EXTREME://EX/Service/Order/Target' ON CONTRACT [EXTREME://EX/Contract/Order] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @DialogHandle MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data); --END CONVERSATION @DialogHandle COMMIT ; END TRY BEGIN CATCH ROLLBACK TRAN; DECLARE @msg VARCHAR(MAX); SELECT @msg = ERROR_MESSAGE(); PRINT @msg END CATCH; GO --Create Initiator Service CREATE SERVICE [EXTREME://EX/Service/Order/Initiator] ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order]) GO --Create Target Service CREATE SERVICE [EXTREME://EX/Service/Order/Target] ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order]) GO
Now, we will define one trigger which will call SB to check whether order is duplicate or not every time we insert new record (not calling trigger for Update, you can do it if it is your business need)
-------service broker call from trigger. CREATE TRIGGER trg_OrderDetail_Ins ON OrderDetail AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @Data XML SELECT @Data = ( SELECT 'OrderHistory' AS 'Header/@MessageType', OrderID AS 'Header/@OrderID', ClientID AS 'Header/@ClientID', ProjectID AS 'Header/@ProjectID' FROM (SELECT * FROM inserted) AS T FOR XML PATH(''), ROOT('OrderMessage'), TYPE ) EXEC [SendOrderRequestMessage] @Data END GO
Now, we are ready with service broker architecture and ready to test whether SB is working or not.
--Testing SB by inserting one record in OrderDetail Table --which will execute Trigger and trigger will activate SB INSERT INTO OrderDetail(ClientID,ProjectID) SELECT 'TC01','NY Healthcare' GO --you will get one record which we have inserted above. SELECT * FROM OrderDetail GO --you will get no record in OrderHistory table --as we have not inserted duplicate record yet. SELECT * FROM OrderHistory GO --inserting same record again so SB will detect it as duplicate order --that will be inserted in ORderHistory table INSERT INTO OrderDetail(ClientID,ProjectID) SELECT 'TC01','NY Healthcare' GO --you will get one record in OrderHistory table --as we have inserted duplicate record SELECT * FROM OrderHistory GO
I have not handled Error properly in CATCH block of Stored Procedure but just printed error message on screen because error handling is out of scope of this article, If you want to handle Error properly in Stored Procedure, have a look at my earlier article here.
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.
Service Broker implementation between two tables in one database http://t.co/C2jwxa0arj
Pingback: Service Broker Queue Activation and Thread in SQL Server
Pingback: Setup service broker between two databases in SQL Server.
Pingback: Count active Thread for Service Broker Queue in SQL Server