SQL Server 2005 Beta 2 Service Broker: Create Route
Posted by: Rickie Lee (www.cnblogs.com/rickie)
Date: Apr. 2005
Following the previous post, A simple tutorial on SQL Server 2005 Beta 2 Service Broker, this post will handle how to send messages to and from in the different Database or SQL Server.
1. Create a new route to the routing table for the current database (initiator)
For outgoing messages, Service Broker determines routing by checking the routing table in the local database.
For example,
Use [Initiator DatabaseName]
CREATE ROUTE HelloRoute
WITH
SERVICE_NAME = 'SendingService',
BROKER_INSTANCE = 'a727462b-52e7-4405-9eee-d19923729790',
ADDRESS = 'LOCAL' ;
The broker_instance parameter must be the broker instance identifier for the remote database, which can be obtained by running the following query in the selected database:
Use [Remote DatabaseName]
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()
2. Create Target Service in the remote database
In order for a message to be accepted at the target Endpoint’s service, three conditions must be satisfied that pertain to the Message Type:
(1) The initiator and target’s Message Type names must be exactly the same;
(2) The message must pass validation against its message body (the type of data it contains);
(3) The message Type used must be allowed by the Dialog conversation’s assigned Contract.
For example,
Use [Remote DatabaseName]
Create Message Type XMLMessage
Validation = WELL_FORMED_XML
Create Contract XMLContract
(XMLMessage Sent by ANY)
Create Queue ReceivingQueue With Status=ON, Retention=OFF
Create Service ReceivingService On Queue ReceivingQueue
(XMLContract)
3. Send message at the initiator service
Well, we know the initiator side must also contains all necessary Service Broker types, such as Message Type, Contract, Queue and Service, etc.
For example,
Declare @handle uniqueidentifier
Begin Dialog Conversation @handle
From Service SendingService
To Service 'ReceivingService'
On Contract XMLContract;
Send on Conversation @handle
Message Type XMLMessage
('<hello>Welcome to Rickie Lee''s blog, www.cnblogs.com/rickie</hello>');
End Conversation @handle with cleanup;
4. Receive message at the target side
Let’s check if the target queue has received the message.
For example,
Select * From ReceivingQueue
Select Cast(message_body as XML) From ReceivingQueue
-- Receive message from target queue
Receive Cast(message_body as XML) From ReceivingQueue
References:
1. A First Look at SQL Server 2005 Service Broker, Roger Wolter, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp?frame=true
2. Michael G., Service Broker Architecture, http://www.dotnetfun.com/articles/sql/sql2005/SQL2005ServiceBrokerBasicArchitecture.aspx
3. Mike Taulty’s Weblog, SQL Server 2005 Service Broker & WSE 2.0, http://mtaulty.com/blog/archive/2005/02/14/1484.aspx