DevWeek 2006 - my simple Service Broker example

  • Article

I delivered a session to a full house on Tuesday which included a small demo on Service Broker. A big thanks to all who showed. I hope to see some of you again on Thursday in my intro to BI session.

I promised to post the code - enjoy.

CREATE DATABASE TestSB
go

USE TestSB
go

ALTER DATABASE TestSB SET ENABLE_BROKER
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password01'
go

-- SETUP: setup the infrastructure to send a message via service broker
-- Need a sender Q (TxQ) and receiver Q (RxQ)

CREATE QUEUE TxQ
CREATE QUEUE RxQ

-- Need a message type to send
CREATE MESSAGE TYPE Msg

-- Need a contract that says who can send the message type
CREATE CONTRACT MsgContract(Msg SENT BY ANY)

-- Need services to look after the Qs
CREATE SERVICE TxSvc ON QUEUE TxQ
CREATE SERVICE RxSvc ON QUEUE RxQ (MsgContract)

-- TEST IT: Send one message - need to send as part of a dialog
DECLARE @h uniqueidentifier

BEGIN DIALOG CONVERSATION @h
FROM SERVICE TxSvc TO SERVICE 'RxSvc'
ON CONTRACT MsgContract;

SEND ON CONVERSATION @h MESSAGE TYPE Msg ('Hello');
SEND ON CONVERSATION @h MESSAGE TYPE Msg ('World');

END CONVERSATION @h;

-- Check the destination Q

SELECT * FROM RxQ

SELECT message_type_name, CAST(message_body as VARCHAR) as Body FROM RxQ

-- Troubleshooting

SELECT * FROM sys.transmission_queue
SELECT transmission_status FROM sys.transmission_queue

-- TEST IT: Run this in a SECOND Window to wait for a message on the destination q

WAITFOR (RECEIVE TOP(1)
CAST(message_body as VARCHAR)
FROM RxQ);