This puts some messages on the queue by calling the Proc_Queue_Send_Message stored procedure (which completes quickly). It then calls the Proc_Queue_Process_Messages stored procedure; this reads messages off the queue and processes them, putting the results in the T_QueueProcessingResult table. Proc_Queue_Process_Messages mimics doing “hard work…

Service Broker 02 - Write to Queue and Process

Use ServiceBrokerDemo
GO

Set NoCount On

-- Clear out any previous results of processing the queue's messages.
Delete dbo.T_QueueProcessingResult

RaisError ('Adding messages to the queue ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Send_Message
	@Message = '<row StatusID="2" Status="Modified"/><row StatusID="1" Status="New"/><row StatusID="3" Status="Unmodified"/>',
	@FromService = 'Service_Demo',
	@ToService = 'Service_Demo',
	@OnContract = 'Contract_Demo'

RaisError ('Adding more messages ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Send_Message
	@Message = '<row StatusID="27" Status="Anonymised"/>',
	@FromService = 'Service_Demo',
	@ToService = 'Service_Demo',
	@OnContract = 'Contract_Demo'

RaisError ('Processing these messages. This will take ~5s per row ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Process_Messages

RaisError ('', 0, 1) With NoWait
RaisError ('Adding another message to the queue ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Send_Message
	@Message = '<row StatusID="999" Status="Defective"/>',
	@FromService = 'Service_Demo',
	@ToService = 'Service_Demo',
	@OnContract = 'Contract_Demo'

RaisError ('Processing it (another 5s) ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Process_Messages

-- Then have a look at the results of the processing.
Select * From dbo.T_QueueProcessingResult