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

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;

SELECT * FROM RxQ

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

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