SQL Server Service Borker 1

標籤：blog   http   io   ar   使用   for   sp   strong   資料   

1、訊息類型定義：

　　訊息類型，是資訊交換的模板、create message type message_type_name validattion = well_formed_xml;

2、約定定義：

　　約定，指示任務使用的訊息 create contract contract_name (message_type_name sent by initiator |target | all [....]);

3、隊列定義：

　　隊列是資訊的集合： create queue queue_name with status = on;

4、服務定義：

　　服務定義連接埠用來把訊息綁定到一個或多個約定上 create service service_name on queue queue_name(contract_name_list);

例子：

create database bookstore;
create database bookdistribution;

----時間執行者目的

----2014-10-30蔣樂建立兩個資料庫用於測試 service broker!
go

use bookstore;

alter database bookstore
set enable_broker;

alter database bookstore
set trustworthy on;

create master key
encryption by password =‘123456‘;

----時間執行者目的

----2012-10-30蔣樂配製資料庫的 3(enable_broker\trustworthy\master key) 個選項使它支援 service borker
go

use bookdistribution;

alter database bookdistribution
set enable_broker;

alter database bookdistribution
set trustworthy on;

create master key
encryption by password = ‘123456‘;

----時間執行者目的

----2012-10-30蔣樂配製資料庫的 3(enable_broker\trustworthy\master key) 個選項使它支援 service borker

go

use bookstore;

create message type [send_to_distribution]
validation = well_formed_xml;

create message type [send_to_distribution_received]
validation = well_formed_xml;

create contract [send_to_distribution_contract](
[send_to_distribution] sent by initiator,
[send_to_distribution_received] sent by target);

----時間執行者目的

----2012-10-30蔣樂定義訊息類型與約定、（注意在兩個相互連信的資料庫中
--要有一樣的訊息類型與約定才可以通訊。
go

use bookdistribution;

create message type [send_to_distribution]
validation = well_formed_xml;

create message type [send_to_distribution_received]
validation = well_formed_xml;

create contract [send_to_distribution_contract](
[send_to_distribution] sent by initiator,
[send_to_distribution_received] sent by target);

----時間執行者目的

----2012-10-30蔣樂定義訊息類型與約定。
go

use bookstore;

create queue [send_to_bookdistribution_queue]
with
status = on;

----時間執行者目的

----2012-10-30蔣樂定義隊列
go

use bookdistribution;

create queue [send_to_bookdistribution_queueBookDistribution]
with
status = on;

 

----時間執行者目的

----2012-10-30蔣樂定義隊列
go

use bookstore;

create service [send_to_distribution_service_bookstore]
on queue send_to_bookdistribution_queue(send_to_distribution_contract);

 

----時間執行者目的

----2012-10-30蔣樂定義服務
go

use bookdistribution

create service [send_to_distribution_service_bookdistribution]
on queue send_to_bookdistribution_queueBookDistribution(send_to_distribution_contract);

----時間執行者目的

----2012-10-30蔣樂定義服務
go

use bookstore;

declare @handle uniqueidentifier;
declare @message xml;

begin dialog conversation @handle
from service send_to_distribution_service_bookstore
to service ‘send_to_distribution_service_bookdistribution‘ -- ! _ ! -- :因為它定義在別的資料庫中、所以不可以用名字。要用字串。
on contract send_to_distribution_contract;

set @message = ‘<Person>11436101</Person>‘;

send on conversation @handle message type send_to_distribution
(@message);

----時間執行者目的

----2012-10-30蔣樂發送一個非同步訊息
go

use bookdistribution;

select * from dbo.send_to_bookdistribution_queueBookDistribution;

----時間執行者目的

----2012-10-30蔣樂查看發送過來的訊息
go

 

SQL Server Service Borker 1