Service Broker implementation between two tables in one database

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.

[sourcecode language=”sql”]
–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
[/sourcecode]

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.

[sourcecode language=”sql”]
–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
[/sourcecode]

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)

[sourcecode language=”sql”]
——-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
[/sourcecode]

Now, we are ready with service broker architecture and ready to test whether SB is working or not.

[sourcecode language=”sql”]
–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
[/sourcecode]

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

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.