DevWeek 2006 – my simple Service Broker example


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);

Comments (0)