0

I have the following procedure

USE ServiceBrokerTest;  
GO  
CREATE PROCEDURE PublishNotification  
    @NotificationData VARCHAR(1000)  
AS   
    DECLARE @InitDlgHandle UNIQUEIDENTIFIER
    BEGIN TRAN 
    --Determine the Initiator Service, Target Service and the Contract 
    BEGIN DIALOG @InitDlgHandle
    FROM SERVICE [//SBTest/NotificationsService]
    TO SERVICE'//SBTest/NotificationsService'
    ON CONTRACT [//SBTest/NotificationContract]
    WITH ENCRYPTION=OFF; 
    --Send the Message
    SEND ON CONVERSATION @InitDlgHandle 
    MESSAGE TYPE [//SBTest/NotificationMessage](@NotificationData);
    COMMIT TRAN GO

Which I run with 

EXECUTE PublishNotification N'<root><first type="string">lorem</first><second type="integer">0</second></root>';

Now I'd like to extract also the service to use (in the code above //SBTest/NotificationsService) since I have multiple services. Note All services use the same NotificationContract and NotificationMessage.

How can I do that?

Improve this question
  • Do you mean you want the FROM and TO services to be dynamic rather than hard-coded in the proc? How are those determined? Seems odd to have the same service as both the initiator and target in your example. – Dan Guzman Jul 3 '18 at 12:52
  • Hi @DanGuzman, Yes. My idea is to create 2 different triggers which both use the procedure. The trigger will know the service to call. I'm new to SQL Server, I need to create pub-sub without getting replies back so I thought using the same service for the initiator and target would be ok. – Sig Jul 3 '18 at 12:56
  • You don't want to fire and forget with service broker or you will leak conversation handles. The same service could be in both initiator and target roles but would need to end both sides of the conversation. One needs to follow proper design patterns with SB and I think this is a tip-of-the-iceberg question for someone new to both SQL Server and Service Broker. The need for dynamic service names is a code smell. – Dan Guzman Jul 3 '18 at 13:27
  • I do agree with you. Unfortunately, I don't have the time to become a certified SQL Server Admin. Thanks for the link. – Sig Jul 3 '18 at 13:34
  • 1
    Remus has many good SB articles on his site. I suggest you peruse those. – Dan Guzman Jul 3 '18 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.