How to Communicate Between SSBS Applications Across Instances

Communication
across instances – Sending message from Initiator

USE SSBSInitiatorDB;
GO
--To send message, first you need to initiate a dialog with 
--BEGIN DIALOG command and specify the Initiator and Target
--services which will be talking in this dialog conversation
DECLARE @SSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RequestMessage XML;
BEGIN TRANSACTION;
       BEGIN DIALOG @SSBSInitiatorDialogHandle
               FROM SERVICE
                [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
               TO SERVICE
                N'//SSBSLearning/ProductStockStatusCheck/TargetService'
               ON CONTRACT
                [//SSBSLearning/ProductStockStatusCheckContract]
               WITH ENCRYPTION = ON;
       SELECT @RequestMessage =
                 N'<Request>
                           <ProductID>316</ProductID>
                           <LocationID>10</LocationID>
                     </Request>';
       --To send message you use SEND command and specify the dialog
       --handle which you got above after initiating a dialog        
       SEND ON CONVERSATION @SSBSInitiatorDialogHandle
               MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
               (@RequestMessage);
       SELECT @RequestMessage AS RequestMessageSent;
COMMIT TRANSACTION;
GO
--If in case message cannot be put into Target queue becuase
--Target queue is not enabled or because of any other reasons
--it will be temporarily put into transmission queue until 
--its delivery to Target queue
SELECT * FROM sys.transmission_queue
GO

USE SSBSTargetDB;
GO
--You can query the Target queue with SELECT statement
SELECT * FROM dbo.SSBSLearningTargetQueue;
GO
--To retrieve a message from a queue you use RECEIVE command,
--With every message you also get dialog handle which you can 
--use to reply back to sender of the message
DECLARE @SSBSTargetDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvdRequestMessage XML;
DECLARE @RecvdRequestMessageTypeName sysname;
BEGIN TRANSACTION;
       --WAITFOR command is used to wait for messages to arrive
       --on the queue, TIMEOUT is specified in miliseconds
       WAITFOR
       ( RECEIVE TOP(1)
              @SSBSTargetDialogHandle = conversation_handle,
              @RecvdRequestMessage = CONVERT(XML, message_body),
              @RecvdRequestMessageTypeName = message_type_name
         FROM dbo.SSBSLearningTargetQueue
       ), TIMEOUT 5000;
 
       SELECT @RecvdRequestMessage AS RequestMessageReceived;
       --If the message type is request from Initiator, process the request.
       IF @RecvdRequestMessageTypeName = N'//SSBSLearning/ProductStockStatusCheckRequest'
       BEGIN
               DECLARE @ReplyMessage NVARCHAR(max);
               DECLARE @Quantity smallint
               SELECT @Quantity = Quantity 
                     FROM AdventureWorks.Production.ProductInventory
                     WHERE ProductID = @RecvdRequestMessage.value('(/Request/ProductID)[1]', 'int')  
                     AND LocationID = @RecvdRequestMessage.value('(/Request/LocationID)[1]', 'int') 
               SELECT @ReplyMessage = 
               N'<Reply>
                     <Quantity>' + CONVERT(VARCHAR(10), @Quantity) + '</Quantity>
               </Reply>';
               --To send message back to sender you again use SEND command and specify the dialog
               --handle which you got above while retrieving the message from the queue
               SEND ON CONVERSATION @SSBSTargetDialogHandle
                       MESSAGE TYPE 
                       [//SSBSLearning/ProductStockStatusCheckResponse]
                       (@ReplyMessage);
               --To end a dialog you use END CONVERSATION command, here the dialog
               --is being ended by Target, and then a message of 
               -- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message type 
               --will be sent to Initiator
               END CONVERSATION @SSBSTargetDialogHandle;
       END
       SELECT @ReplyMessage AS ReplyMessageSent;
COMMIT TRANSACTION;
GO
--Again if there is any error during transmission for messages from Target to 
--Initiator, the messages will be temporarily put into transmission queue until 
--its delivery to Initiator queue
SELECT * FROM sys.transmission_queue
GO

USE SSBSInitiatorDB
GO
--You can query the Initiator queue with SELECT statement
--Here you will see two records, one is the response and another
--one is the end dialog intimation with NULL in its message_body column
SELECT * FROM dbo.SSBSLearningInitiatorQueue
GO
--At this point the Initiator queue will hold two records, first
--one is a response message for the request and another one is for 
--intimation that dialog has been ended by the Target.
--You need to execute below piece of code twice to retrive both the 
--records from Initiator queue, if the message is of type 
-- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog the dialog will
--be ended by Intiator also or else response message will be processed. 
DECLARE @RecvSSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvReplyMessage NVARCHAR(100);
DECLARE @RecvMessageType SYSNAME
BEGIN TRANSACTION;
       WAITFOR
       ( RECEIVE TOP(1)
              @RecvSSBSInitiatorDialogHandle = conversation_handle,
              @RecvReplyMessage = message_body,
              @RecvMessageType = message_type_name
         FROM dbo.SSBSLearningInitiatorQueue
       ), TIMEOUT 5000;
       --If the message is of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog 
       --the dialog will be ended by Intiator also or else response message will be processed.
       IF (@RecvMessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
              END CONVERSATION @RecvSSBSInitiatorDialogHandle;
       ELSE
              SELECT @RecvReplyMessage AS ReplyMessageRecived;
COMMIT TRANSACTION;
GO

USE master;
GO
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'SSBSInitiatorEP')
     DROP ENDPOINT SSBSInitiatorEP;
GO
USE msdb
GO
IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSInitiatorRoute')
     DROP ROUTE SSBSInitiatorRoute;
GO
USE SSBSInitiatorDB;
GO
--Cleanup code to drop SSBS objects individually which 
--you created above, notice the order of dropping the objects,
--its reverse of the order in which you created the objects
DROP ROUTE SSBSTargetRoute;
DROP REMOTE SERVICE BINDING SSBSRemoteServiceBindingForTarget;
DROP CERTIFICATE SSBSTargetCert;
DROP USER SSBSTargetUser;
GO
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorService];
DROP QUEUE dbo.SSBSLearningInitiatorQueue;
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract];
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest];
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse];
DROP CERTIFICATE SSBSInitiatorCert;
DROP MASTER KEY;
DROP USER SSBSInitiatorUser;
GO
--Drop the Initiator database which you created above
USE master
GO

USE master;
GO
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'SSBSTargetEP')
     DROP ENDPOINT SSBSTargetEP;
GO
USE msdb
GO
IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSTargetRoute')
     DROP ROUTE SSBSTargetRoute;
GO
USE SSBSTargetDB;
GO
--Cleanup code to drop SSBS objects individually which 
--you created above, notice the order of dropping the objects,
--its reverse of the order in which you created the objects
DROP ROUTE SSBSInitiatorRoute;
DROP REMOTE SERVICE BINDING SSBSRemoteServiceBindingForInitiator;
DROP CERTIFICATE SSBSInitiatorCert;
DROP USER SSBSInitiatorUser;
GO
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
DROP QUEUE dbo.SSBSLearningTargetQueue
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse]
DROP CERTIFICATE SSBSTargetCert;
DROP MASTER KEY;
DROP USER SSBSTargetUser;
GO
--Drop the database which you created above
USE master
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSTargetDB')
       DROP DATABASE SSBSTargetDB
GO