CODEWITHSUNDEEP
sqlsql-serverservice-broker
Craig
Craig

Reputation: 18734

SQL Service Broker example not working

I have two databases on the same instance.

One called ICMS, one called CarePay_DEV1

When a change happens in ICMS (Source), it needs to send a message to CarePay_Dev1 (Destination).

I am new to Broker Services, and am trying to make a message go to the queue. Once that works, I will hopefully get the data into a table in the destination, and that will then be processed by .Net code. But I just want something to appear in the destination first.

So, step 1: I enable the service on the two databases

-- Enable Broker on CarePay
ALTER DATABASE CarePay_Dev1
SET ENABLE_BROKER; 

-- Enable Broker on Source
ALTER DATABASE ICMS_TRN
SET ENABLE_BROKER;

Step 2: Create the message type on the source and destination.

-- Create Message Type on Receiver:
USE CarePay_DEV1
GO
CREATE MESSAGE TYPE [IcmsCarePayMessage]
VALIDATION=WELL_FORMED_XML;

-- Create Message Type on Sender:
USE ICMS_TRN
GO
CREATE MESSAGE TYPE [IcmsCarePayMessage]
VALIDATION=WELL_FORMED_XML;

I then create the Contacts, on both databases:

-- Create Message Type on Receiver:
USE CarePay_DEV1
GO
CREATE MESSAGE TYPE [IcmsCarePayMessage]
VALIDATION=WELL_FORMED_XML;

-- Create Message Type on Sender:
USE ICMS_TRN
GO
CREATE MESSAGE TYPE [IcmsCarePayMessage]
VALIDATION=WELL_FORMED_XML;

I then create the message queues on both databases:

-- CREATE Sending Messagw Queue
USE ICMS_TRN
GO
CREATE QUEUE CarePayQueue


-- CREATE Receiving Messagw Queue
USE CarePay_Dev1
GO
CREATE QUEUE CarePayQueue

And finally, I create the services on both databases:

-- Create the message services
USE ICMS_TRN
GO
CREATE SERVICE [CarePayService]
ON QUEUE CarePayQueue


USE CarePay_DEV1
GO
CREATE SERVICE [CarePayService]
ON QUEUE CarePayQueue

Now, the queues should be ready, so then I try and send something from the source to the destination:

-- SEND THE MESSAGE!
USE ICMS_TRN
GO

DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000) 

BEGIN TRAN

    BEGIN DIALOG @InitDlgHandle
    FROM SERVICE [CarePayService]
    TO SERVICE 'CarePayService'
    ON CONTRACT [IcmsCarePayContract]

    SELECT @RequestMessage = N'<Message>The eagle has landed!</Message>';

    SEND ON CONVERSATION @InitDlgHandle
    MESSAGE TYPE [IcmsCarePayMessage] (@RequestMessage)

COMMIT TRAN

I get:

Command(s) completed successfully.

But then when I try select from the destination queue, it's empty.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM [CarePay_DEV1].[dbo].[CarePayQueue] WITH(NOLOCK)

Can anyone spot the issue? I can't see where I tell the destination which database to send the message to - which could be part of the issue?

Upvotes: 3

Views: 2916

Answers (2)

etliens
etliens

Reputation: 1342

I highly recommend you read Adam Machanic's Service Broker Advanced Basics Workbench, specifically the section entitled "Routing and Cross-Database Messaging".

In addition, for future troubleshooting you may want to use SSBDiagnose or also read through Remus Rusanu's numerous articles on the topic

Upvotes: 3

yuriy
yuriy

Reputation: 264

I think the initiator service sent a message to yourself. Try to change the name of destination (terget) service.

Upvotes: 1

Related Questions