-- example to illustrate asynchronous processing
-- of messages in a queue. Example uses a sender
-- sends postcard to receiver. MailBot stored proc
-- called by Server Broker when ever mail arrives
-- to pick up mail from mail box


use master

drop database OneWayAsync
go

-- make a database to hold example
Create database OneWayAsync
go

use OneWayAsync
go

-- we will send a post card from
-- one service to another,
-- the other service won't have to keep
-- checking the mail box, Service
-- Manager will will wake up the service
-- is a message type for a postcard
Create Message Type PostCard
validation = none
go


-- We need a contract that
-- specifies that a postcard
-- can be sent.
-- Note that only an INITIATOR
-- can send a post card
create Contract Mail
(
PostCard SENT BY INITIATOR
)
go

create table postcards
(
id int identity primary key,
message nvarchar(max),
message_type nvarchar(256)
)

go

-- MailBot stored proc that picks up
-- mail when Service Broker calles it
create PROCEDURE MailBot
as
go
alter PROC MailBot
AS
-- we need to get the service instance
declare @service_id uniqueidentifier

;GET SERVICE INSTANCE @service_id FROM MailBox

-- let's see if the message made it
-- we will need a variable to hold the result
declare @postcardMessage varbinary(max)
-- wait around by the mail box for
-- something to arrive
-- This queue could hold messages from
-- many difference service instances, we want
-- to make sure that we get the right one. In
-- the previous example we kinda' ignored this
-- fact just to make the example less tedious
declare @messageType nvarchar(256)
declare @messageTypeId int
WAITFOR (
receive TOP(1)  @postcardMessage = message_body, @messageType = message_type_name 
from MailBox where service_instance_id = @service_id), TIMEOUT 5

-- let's read the postcard we received
if @messageType = N'PostCard'
insert into postcards values (convert(nvarchar(max), @postcardMessage), @messageType)
go


-- a post office is a place to send postcards
create QUEUE PostOffice
-- the mail box has a MailBot that
-- picks up the mail for you
-- when mail arrives Service Broker
-- will call the MailBot stored procedure
create QUEUE MailBox
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = MailBot,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
)
go

-- now we need some services.
-- we need two services because
-- messages can only be sent 
-- between services
-- note that the send will receive
-- error in the post office queue
Create service Sender ON QUEUE PostOffice
go


-- Receiver is intended to receive post card
-- so it requires a contract that says it
-- can receive post cards. The receiver
-- will receive post cards in the MailBox
-- Queue. The contract only allows post cards,
-- no bills or junk mail are allowed
Create service Receiver ON QUEUE MailBox (Mail)


-- >>>>>>>>>>>>>>>>>>>> Async Test <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

-- a dialog is required to send something
-- we will  need a place to hold the
-- identifier of our dialog
declare @dialog uniqueidentifier

-- now must begin a dialog between
-- the sender and receiver
BEGIN DIALOG @dialog FROM SERVICE Sender TO SERVICE 'Receiver'
ON CONTRACT Mail 


-- now we can send a post card
-- lets make up a messsage to send
declare @message nvarchar(max)
set @message = N'Wish you were here.'

-- stick the message onto the postcard and send it
-- not that Service Broker know were to send the 
-- message because from and two were defined 
-- in the dialog, identified by the @dialog
-- variable
;send ON CONVERSATION @dialog MESSAGE TYPE PostCard
(convert(varbinary(max), @message))


-- wait an see
WAITFOR DELAY '00:00:2'
select * from postcards

