Asynchronous message delivery based on Service Broker

This demonstrates asynchronous messaging based on Service Broker between different databases in the same SQL Server, where stored procedure serves as the service program. HELLOWORLDDB is the target database, and DOTNETFUN2 is the database that sent the message.

Also, assume that the base object type of server broker has been created, such as MessageType (Xmlmessage), Contract (Xmlcontract), Queue (Sendingqueue and Receivingqueue), and so on, you can refer to "A simple tutorial on SQL Server 2 Service Broker." In addition, because of messaging between different databases, you need to create Route to refer to SQL Server 2 Service broker:create Route.

1. Create stored procedure as internal Service program.

use HELLOWORLDDB
Go
Alter Procedure helloworldresponder
as
Begin
Declare @ Conversationhandle uniqueidentifier
Declare @message_body nvarchar (MAX)
Declare @message_type_name SYSNAME
while (1=1)
is begin
begin TRANSACTION
--wait for 1 seconds for messages to arrive
WAITFOR (
– for Simpl Icity We process one message at a time
RECEIVE top (1)
@message_type_name =message_type_name,
@ Conversationhandle=conversation_handle,
@message_body =message_body
from [Receivingqueue]), TIMEOUT 1000
--If A message is received, process it, else skip
If (@ @rowcount <= 0)
break;
--If This is a XML message,
--respond with a appropriate greeting
if @message_type_name = ' xmlmessage '
beginsend on conversation @conversationHandle
message TYPE xmlmessage
(' End conversation @conversationHandle
End
Commit
End
Commit
End
Go

The stored procedure is responsible for retrieving messages from the Receivingqueue and, depending on the retention settings of the queue, determines the status of removing messages from the queue or updating messages in the queue.

2. Set the activation mechanism for the target queue

Use HelloWorldDB
go
ALTER QUEUE [ReceivingQueue] WITH
ACTIVATION (
STATUS = ON, -- Turn on internal activation
PROCEDURE_NAME = [HelloWorldResponder], -- Our stored proc
MAX_QUEUE_READERS = 4, -- Up to 4 concurrent readers
EXECUTE AS SELF)　-- Execute as user of incoming dialog

Sets the stored Procedure that is created above, and the stored Procedure is activated and processes messages in the queue.

3. Send a message at the initiator end

Use DotNetFun2
go
DECLARE @conversationHandle uniqueidentifier
BEGIN TRANSACTION
-- Begin a dialog to the Hello World Service
BEGIN DIALOG　@conversationHandle
FROM SERVICE　　[SendingService]
TO SERVICE　　　'ReceivingService','a727462b-52e7-4405-9eee-d19923729790'
ON CONTRACT　　 [XMLContract]
WITH ENCRYPTION = OFF, LIFETIME = 600;
-- Send message
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [XMLMessage]
('Select * From sys.conversation_endpoints
COMMIT

of which, to service ' receivingservice ', ' a727462b-52e7-4405-9eee-d19923729790 ', ' Receivingserice ' indicates the target SERVICE name, ' a727462b-52e7-4405-9eee-d19923729790 ' specifies the database in which the target service resides, which can be obtained by using the following SQL script:

-- Retrieve remote broker instance guid
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID('HelloWorldDB')

In addition, the following SQL script can be used to detect the reply message received by the initiator side:

Select cast(message_body as XML) From SendingQueue
Receive message_type_name,
cast(message_body as XML)
From SendingQueue

4. Query dialog endpoint status (state of conversation endpoints)

Finally, you can get the dialog dialog state by querying the Sys.conversation_endpoints table at the Target/initiator end:

Select * From sys.conversation_endpoints