SQL 2025 master class LAB Service Broker

--step 1

USE master
GO

IF EXISTS ( SELECT  *
            FROM    sys.databases
            WHERE   name = 'SoftwareSmithServiceBroker' )
    BEGIN
            DROP DATABASE SoftwareSmithServiceBroker ;
    END
GO

CREATE DATABASE SoftwareSmithServiceBroker
GO

USE SoftwareSmithServiceBroker
GO

ALTER DATABASE SoftwareSmithServiceBroker SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE SoftwareSmithServiceBroker SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SoftwareSmithServiceBroker SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO

--step 2 

CREATE MESSAGE TYPE
[SoftwareSmithRequestMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE MESSAGE TYPE
[SoftwareSmithResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE CONTRACT [SoftwareSmithContract]
(
[SoftwareSmithRequestMessage] SENT BY INITIATOR,
[SoftwareSmithResponseMessage] SENT BY TARGET
) 

GO

--step 3 

CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue 
(
[SoftwareSmithContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
[SoftwareSmithContract]
)
GO  

--step 4 

BEGIN TRANSACTION ;
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @msg NVARCHAR(MAX) ;

BEGIN DIALOG CONVERSATION @ch
      FROM SERVICE [InitiatorService]
      TO SERVICE 'TargetService'
      ON CONTRACT [SoftwareSmithContract]
      WITH ENCRYPTION = OFF ;

SET @msg = '<SoftwareSmithRequest>
               Software-smith
      </SoftwareSmithRequest>' ;

SEND ON CONVERSATION @ch 
   MESSAGE TYPE [SoftwareSmithRequestMessage] 
   (@msg) ;
COMMIT TRANSACTION
GO

--step 5

SELECT message_type_name, CAST(message_body AS XML) FROM TargetQueue
GO 

DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @responsemessage XML

BEGIN TRANSACTION ;

RECEIVE TOP(1)
@ch = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM TargetQueue

PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
PRINT 'Message type: ' + @messagetypename
PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))

IF ( @messagetypename = 'SoftwareSmithRequestMessage' ) 
    BEGIN
-- Construct the response message
        SET @responsemessage = '<SoftwareSmithResponse>Broker response messsage, '
            + @messagebody.value('/SoftwareSmithRequest[1]', 'NVARCHAR(MAX)')
            + '</SoftwareSmithResponse>' ;

-- Send the response message back to the initiating service
        SEND ON CONVERSATION @ch MESSAGE TYPE [SoftwareSmithResponseMessage] (@responsemessage) ;

-- End the conversation on the target's side
        END CONVERSATION @ch ;
    END
COMMIT TRANSACTION
GO 

--step 6

--SELECT message_type_name , CAST(message_body AS XML)FROM InitiatorQueue
SELECT * FROM InitiatorQueue
GO

DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML

BEGIN TRANSACTION ;
RECEIVE TOP (1)
@ch = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM InitiatorQueue

IF ( @messagetypename = 'SoftwareSmithResponseMessage' ) 
    BEGIN
        PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
        PRINT 'Message type: ' + @messagetypename
        PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
    END

IF ( @messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' ) 
    BEGIN
-- End the conversation on the initiator's side
        END CONVERSATION @ch ;
    END
COMMIT TRANSACTION 
GO
--step 7  run step 6 again

--with thanks and accreditation to https://www.sqlservercentral.com