Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Run from any existing database on a SQL Server, version 2005 or later. It creates a new database called ServiceBrokerDemo and makes in it a Queue, Service, Conversation, Message Type and a couple of example stored procedures.
Use Master
GO
If Not Exists (Select 1 From sys.databases Where (Name = 'ServiceBrokerDemo'))
Begin
Print 'Creating database ...'
Create Database ServiceBrokerDemo
End
GO
If Not Exists (Select 1 From sys.databases Where (Name = 'ServiceBrokerDemo') And (is_broker_enabled = 1))
Begin
Print 'Enabling Service Broker ...'
Alter Database ServiceBrokerDemo Set Enable_Broker
End
GO
Use ServiceBrokerDemo
GO
-- For reasons of performance, we cache Conversation Handles in our own table.
If Exists (Select 1 From sys.objects Where object_id = object_id ('TSys_ConversationHandle') And Type = 'U')
Drop Table dbo.TSys_ConversationHandle
GO
Create Table dbo.TSys_ConversationHandle
(
ConverationHandleID Int Identity (1,1) Not Null,
Handle UniqueIdentifier Not Null,
SPID Int Not Null,
FromService SysName Not Null,
ToService SysName Not Null,
OnContract SysName Not Null,
)
GO
-- A table to record the results of the processing.
If Exists (Select 1 From sys.objects Where object_id = object_id ('T_QueueProcessingResult') And Type = 'U')
Drop Table dbo.T_QueueProcessingResult
GO
Create Table dbo.T_QueueProcessingResult
(
QueueProcessingResultID Int Identity (1,1) Not Null,
StatusID Int Not Null,
Status Varchar (Max) Not Null,
CreatedDate DateTime Not Null Default GetDate ()
)
GO
If Exists (Select 1 From INFORMATION_SCHEMA.ROUTINES Where ROUTINE_SCHEMA = 'dbo' And ROUTINE_NAME = 'Proc_Queue_Process_Messages' And ROUTINE_TYPE = 'PROCEDURE')
Drop Procedure dbo.Proc_Queue_Process_Messages
GO
Create Procedure dbo.Proc_Queue_Process_Messages As
/*
<summary>Simply gets all messages from the Queue_Demo queue, extracts the details (StatusID and Status) and writes them to the T_QueueProcessingResult table.</summary>
<returns>N/A.</returns>
<history>
<item>Julian Fletcher, 02/01/2015. Created.</item>
</history>
*/
Set NoCount On
Declare @ConversationHandle UniqueIdentifier
Declare @StatusID Int
Declare @Status Varchar (Max)
Declare @Messages Table
(
ConversationHandle UniqueIdentifier,
MessageTypeName NVarchar (256),
MessageBody XML
)
Begin Try
Begin Tran;
-- This will get all messages and put them into @Messages.
Receive Conversation_Handle, Message_Type_Name, Message_Body
From Queue_Demo
Into @Messages
-- Process the content of our messages one section (i.e. StatusID and Status data pair) at a time.
Declare curCPLIs Cursor Local Fast_Forward For
Select MB.Row.value ('@StatusID', 'int'),
MB.Row.value ('@Status', 'varchar (max)')
From @Messages M
Cross Apply MessageBody.nodes ('/row') MB (Row)
Where (M.MessageTypeName = N'MessageType_Demo')
Open curCPLIs
Fetch Next From curCPLIs Into @StatusID, @Status
While (@@Fetch_Status = 0)
Begin
-- Mimic this taking a long time.
WaitFor Delay '00:00:05'
Insert Into dbo.T_QueueProcessingResult (StatusID, Status)
Values (@StatusID, @Status)
Fetch Next From curCPLIs Into @StatusID, @Status
End
Close curCPLIs
Deallocate curCPLIs
-- Process errors, if there are any.
Declare curErrors Cursor Local Fast_Forward For
Select ConversationHandle
From @Messages
Where MessageTypeName In (N'http://schemas.microsoft.com/SQL/ServiceBroker/Error', N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
Open curErrors
Fetch Next From curErrors Into @ConversationHandle
While (@@Fetch_Status = 0)
Begin
End Conversation @ConversationHandle
Fetch Next From curErrors Into @ConversationHandle
End
Close curErrors
Deallocate curErrors
Commit
End Try
Begin Catch
Declare @ErrorNumber Int
Declare @ErrorLine Int
Declare @ErrorMessage NVarchar (4000)
If (@@TranCount > 0)
Rollback
Select @ErrorNumber = Error_Number (), @ErrorLine = Error_Line (), @ErrorMessage = Error_Message ()
RaisError ('There has been a "%s" error %d at line %d', 15, 1, @ErrorMessage, @ErrorNumber, @ErrorLine)
End Catch
GO
If Exists (Select 1 From INFORMATION_SCHEMA.ROUTINES Where ROUTINE_SCHEMA = 'dbo' And ROUTINE_NAME = 'Proc_Queue_Send_Message' And ROUTINE_TYPE = 'PROCEDURE')
Drop Procedure dbo.Proc_Queue_Send_Message
GO
Create Procedure dbo.Proc_Queue_Send_Message
(
@Message XML,
@FromService SysName,
@ToService SysName,
@OnContract SysName
) As
/*
<summary>Places a Message on a Queue.</summary>
<param name="@Message">The message, as well-formed XML.</param>
<param name="@FromService">The name of the Service.</param>
<param name="@ToService">Ditto.</param>
<param name="@OnContract">The name of the Contract.</param>
<returns>N/A.</returns>
<history>
<item>Julian Fletcher, 02/01/2015. Created.</item>
</history>
*/
Set NoCount On
Declare @Handle UniqueIdentifier
Begin Try
If (@Message Is Not Null)
Begin
Select @Handle = Handle
From dbo.TSys_ConversationHandle
Where (ToService = @ToService)
And (FromService = @FromService)
And (OnContract = @OnContract)
And (SPID = @@SPID)
-- Confirm the handle exists.
If Not Exists (Select * From sys.Conversation_Endpoints Where (Conversation_Handle = @Handle))
Begin
-- If it has gone for some reason, remove the reference from the table and then go on
-- to create a new one (next step).
Delete dbo.TSys_ConversationHandle
Where (ToService = @ToService)
And (FromService = @FromService)
And (OnContract = @OnContract)
And (SPID = @@SPID)
Set @Handle = Null
End
If (@Handle Is Null)
Begin
Begin Dialog Conversation @Handle
From Service @FromService
To Service @ToService
On Contract @OnContract
With Encryption = Off;
Insert Into dbo.TSys_ConversationHandle (Handle, ToService, FromService, OnContract, SPID)
Values (@Handle, @ToService, @FromService, @OnContract, @@SPID)
-- From BOL: "Starts a timer. When the time-out expires, Service Broker puts a message of type
-- http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer on the local queue for the conversation."
-- In essence, we keep this Conversation going for 20s to avoid having to restart it later.
Begin Conversation Timer (@Handle)
TimeOut = 20
End;
Send On Conversation @Handle
Message Type MessageType_Demo (@Message)
End
End Try
Begin Catch
Declare @ErrorNumber Int
Declare @ErrorLine Int
Declare @ErrorMessage NVarchar (4000)
Select @ErrorNumber = Error_Number (), @ErrorLine = Error_Line (), @ErrorMessage = Error_Message ()
RaisError ('There has been a "%s" error %d at line %d', 15, 1, @ErrorMessage, @ErrorNumber, @ErrorLine)
End Catch
GO
-- We have to drop first because of dependencies.
If Exists (Select 1 From sys.services Where Name = 'Service_Demo')
Drop Service Service_Demo
GO
If Exists (Select 1 From sys.service_contracts Where Name = 'Contract_Demo')
Drop Contract Contract_Demo
GO
If Exists (Select 1 From sys.service_message_types Where Name = 'MessageType_Demo')
Drop Message Type MessageType_Demo
GO
If Exists (Select 1 From sys.service_queues Where Name = 'Queue_Demo')
Drop Queue Queue_Demo
GO
Create Message Type
MessageType_Demo
Authorization dbo
Validation = Well_Formed_XML
GO
Create Queue Queue_Demo
GO
Create Contract Contract_Demo
Authorization dbo (MessageType_Demo Sent By Initiator)
GO
Create Service Service_Demo
Authorization dbo On Queue Queue_Demo (Contract_Demo)
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment