SSB(SQLservice Service Broker) 入門實例引見。本站提示廣大學習愛好者：（SSB(SQLservice Service Broker) 入門實例引見）文章只能為提供參考，不一定能成為您想要的結果。以下是SSB(SQLservice Service Broker) 入門實例引見正文
step 1：perpar database & data
use master
go
Create database Inventory
go
use Inventory
go
Create table inventory
(
TitleId nvarchar(6) not null,
Quantity int not null,
primary key (TitleId,Quantity)
);
go
insert inventory values ('pc1001',100);
insert inventory values ('pc1002',200);
insert inventory values ('pc1003',300);
insert inventory values ('pc1004',400);
step 2: 創立全部SSB的基本框架.
起首創立信息類型
Create Message Type [//mark/v10/types/inventory]
Create Message Type [//mark/v10/types/inventoryresponse]
創立合約
Create contracts [//mark/v10/contracts/inventorycontract]
(
[//mark/v10/types/inventory] send by initiator,
[//mark/v10/types/inventoryresponse] send by target
);
創立隊列與辦事
create queue [inventory client queue];
create service [//mark/v10/services/inventory client]
on queue [inventory client queue]
go
--create inventory service
create queue [inventory queue];
create service [//mark/v10/services/inventory]
on queue [inventory queue]
(
[//mark/v10/contracts/inventorycontract]
);
step 3：發送新聞到新聞隊列中
use Inventory
go
--send message
begin transaction
declare @dialog_id uniqueidentifier
begin dialog conversation @dialog_id
from service [//mark/v10/services/inventory client]
to service '//mark/v10/services/inventory'
on contract [//mark/v10/contracts/inventorycontract]
with encryption = off;
send on conversation @dialog_id message type [//mark/v10/types/inventory]
(
'<InventoryUpdate>
<TitleId>pc1001</TitleId>
<Quantity>102</Quantity>
</InventoryUpdate>'
);
commit transaction;
發送勝利後，可以查詢 inventory_queue
select conversation_handle, cast(message_body as xml) as conversation_body from [Inventory queue]
這裡只是查詢了兩列。
個中conversation_body 就是 我們收回的信息了。
setp 4: 從 inventory queue 中獲得信息並更新數據
use inventory
go
begin transaction
declare @dialog_id uniqueidentifier
declare @message_body xml
declare @amount int;
declare @title nvarchar(128);
receive
@dialog_id = conversation_handle,
@message_body = message_body
from [dbo].[Inventory queue]
print 'handle is' + cast(@dialog_id as nvarchar(90))
print 'message body is' + cast(@message_body as nvarchar(max) )
if(@dialog_id is not null)
begin
set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int');
set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)');
print 'a:'+cast(@amount as nvarchar(100))
print 'b:'+@title;
Update inventory set Quantity = @amount where TitleId = @title;
end
end conversation @dialog_id;
commit transaction;
操作勝利後再去查詢inventory queue的表 遲疑外面的數據曾經被receive 出來 就為空了。
數據同時被更新了
step 5: 接上去做個主動監聽功效，只需有信息發送到隊列，相干隊列，便可以主動履行功效.
起首樹立個存儲進程，（把receive 中的那段代碼拿過去便可以了）
use Inventory
go
--create procedure to receive message and update table
create proc InventoryProc as
begin transaction
declare @dialog_id uniqueidentifier
declare @message_body xml
declare @amount int;
declare @title nvarchar(128);
waitfor(
receive
@dialog_id = conversation_handle,
@message_body = message_body
from [dbo].[Inventory queue]),timeout 5000;
print 'handle is' + cast(@dialog_id as nvarchar(90))
print 'message body is' + cast(@message_body as nvarchar(max) )
if(@dialog_id is not null)
begin
set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int');
set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)');
print 'a:'+cast(@amount as nvarchar(100))
print 'b:'+@title;
Update inventory set Quantity = @amount where TitleId = @title;
end
end conversation @dialog_id;
commit transaction;
go
隊列監聽
alter queue [inventory queue]
with activation(
status = on,
procedure_name= InventoryProc,
max_queue_readers=2,
execute as self
);
go
完成後，只需發送隊列信息後，便可以了
PS： 我們界說了 兩個隊列 還有個 inventory client queue
這個隊列保留的是勝利或許error 提醒。用receive 辦法也能夠提掏出來。
select message_type_name from [Inventory client queue]
以上的 message type service 等信息 
如圖所示，都在這裡.
以上是SSB的入門實例，今後會再寫一篇現實運用上的（以上的sql code 都是純手工輸出，能夠還存在毛病（固然校驗了兩遍）,假如產生毛病，對不起年夜家了。）
