SQL Server Service Broker Distributed System (Chao Songtao) 2005 database programming

1. Create a test database

Create DATABASE Serverbrokertest
On Primary (
Name=serverbrokertest_data,
Filename= ' C:\Program Files\Microsoft SQL Server\mssql11. Sqlexpress\mssql\data\serverbrokertest_data.mdf ',
Size=5,
Maxsize=unlimited,
Filegrowth=1
)
Log on
(
Name=student_log,
Filename= ' C:\Program Files\Microsoft SQL Server\mssql11. Sqlexpress\mssql\data\serverbrokertest_log.ldf ',
Size=1,
Maxsize=20,
Filegrowth=3
)

2. Activate the broker feature of the database

SELECT is_broker_enabled from MASTER. SYS. DATABASES
WHERE name= ' serverbrokertest ';

Success: The description has been activated

If unsuccessful: ALTER DATABASE db_brokertest set Enable_broker activates the BROKER feature to activate it

3. Defining Message Types

--Define message type, must be well-formed XML
Use Serverbrokertest
GO
CREATE MESSAGE TYPE
Messagetypeemail--Message type name
VALIDATION = well_formed_xml--validation as standard XML format

4. Define the contract to define the contract name as the owner of the Contractemail convention is dbo. The SENT by user specifies which endpoint can send a message of the indicated message type. The contract records the messages that the service can use to have a specific session. Each session has two endpoints: the initiator endpoint (the service that initiates the session) and the target endpoint (the service the initiator is contacting). Any represents any.

--defining conventions (message type and delivery direction)

Use Serverbrokertest
GO
CREATE Contract Contractemail
AUTHORIZATION dbo
(Messagetypeemail SENT by any)

5. Defining queues

--Define queue message issuing queue
Use Serverbrokertest
GO
CREATE QUEUE dbo. Queuesendemail

--Defines the queue that the queue receives messages from
Use Serverbrokertest
GO
CREATE QUEUE dbo. Queuereceiveemail

6. Defining services

--Define the service, which serves the message, uses the message to issue the queue
Use Serverbrokertest
GO
CREATE SERVICE Servicesendemail
AUTHORIZATION dbo on QUEUE dbo. Queuesendemail

--Define the service, which is the message receiving service, using the Receive Message queue
Use Serverbrokertest
GO
CREATE SERVICE Servicereceiveemail
AUTHORIZATION dbo on QUEUE dbo. Queuereceiveemail

7. Defining routes

--because you are using the same database on the same SQL Server instance, you can use the default route
Use Serverbrokertest
GO
CREATE ROUTE autocreatedlocal
AUTHORIZATION dbo with ADDRESS = N ' LOCAL '

8. Writing the main program

After defining the service Broker object above, we can write a piece of SQL code to complete the asynchronous delivery and reception of the message.

--Writing the main program
--After defining the service Broker object above, we can write a piece of SQL code to complete the asynchronous delivery and reception of the message
--Open transaction
Use Serverbrokertest
GO
BEGIN TRAN
--Define Session variables
DECLARE @dialogid uniqueidentifier
DECLARE @emailmessage XML
--Start session
BEGIN DIALOG conversation @dialogid
From SERVICE Servicesendemail
To SERVICE ' Servicereceiveemail '
On contract Contractemail
With encryption =off--encryption function off
--Define message variables and content

SET @emailmessage =n ' <emailcontent> Welcome to service Broker </emailcontent> ';

SEND on Conversation @dialogid
MESSAGE TYPE Messagetypeemail
(@emailmessage);
--End of reply
END Conversation @dialogid
--Commit a transaction
COMMIT TRAN
--Query the contents of the queue]

SELECT * FROM dbo. Queuesendemail
SELECT * FROM dbo. Queuereceiveemail

SqlServer2012 on the query queue content is empty, do not know what to do?