SQL Service Broker: The Impatient Way!


For those of you who are interested in creating your first SQL Server 2005 Service Broker conversation but are impatient as I am, just grab the code below and give it a try:


— Enable service broker
alter
database [YOUR_DB_NAME] set enable_broker


— Create a message type. You can also specify


  An XML schema to validate messages of this type:


create message type TestMessageType


       validation = none


go


 


— Create a send and a receive queue


create queue TestSendQueue


create queue TestReceiveQueue


go


 


— Now create a contract for our message type.


— A contract defines the message type


  which can be used by a service broker conversation.


  You can also specify who (initiator or target)


  can send messages of this message type


create contract TestContract


(TestMessageType sent by any)


go


 


— We now need services. A service is particular task that


   allows us to send and receive messages


create service TestSendService


on queue TestSendQueue (TestContract)


 


create service TestReceiveService


on queue TestReceiveQueue (TestContract)


go


 


— The next step is to start a dialog between


  services on a specific contract


declare @conversationHandle uniqueidentifier


begin dialog conversation @conversationHandle


       from service TestSendService


       to service ‘TestReceiveService’


       on contract TestContract


       with encryption = off


go


 


— Now try and send a message using the above conversation


 


— The first step is to get the conversation handle (it is important


—   to note that the same conversation handle returned


—   by the BEGIN DIALOG statement could also be used – just


—   drop the GO statement)


declare @conversationHandle uniqueidentifier


select @conversationHandle = conversation_handle


       from sys.conversation_endpoints


       where far_service = ‘TestReceiveService’;


 


send on conversation @conversationHandle


       message type TestMessageType


(N‘Test Message (‘ + cast(newid() as varchar(36)) + ‘)’)


go


 


— Now receive from the queue


receive convert(nvarchar(max), message_body)


from TestReceiveQueue


go


Good luck!

Comments (0)