SQL2005 讯息队列

Server Broker实现消息的发送

      
(一)同一实例同一数据库下的消息发送

1.准备工作：

1.1创建数据库ssbDemo

use master
go

if exists( select top 1 1
    from sys.sysdatabases with(nolock)
    where name=N'ssbDemo'
   )
begin
drop database ssbDemo
end

create database ssbDemo
go

1.2 允许Server Broker

--check database broker states
declare @is_broker_enabled int
select top 1 @is_broker_enabled=is_broker_enabled
from sys.databases with (nolock)
where database_id=db_id(N'ssbDemo')

if @is_broker_enabled<>1
begin
--Enable SSB
alter database ssbDemo
set enable_broker
alter database ssbDemo
set trustworthy on
end
1.3创建Master Key

use ssbDemo
go
create master key
encryption by password = 'Cw24@$g'

2.创建消息

use ssbDemo
go
--Message
if exists (select top 1 1
    from sys.service_message_types with(nolock)
    where name='HelloWorldRequest')
begin
drop message type HelloWorldRequest;
end
go
create message type [HelloWorldRequest] VALIDATION=NONE

if exists (select top 1 1
    from sys.service_message_types with(nolock)
    where name='HelloWorldRespone')
begin
drop message type HelloWorldRespone;
end
go
create message type [HelloWorldRespone] VALIDATION=NONE

3.创建合约

--Contract
if exists (select top 1 1
    from sys.service_contracts with(nolock)
    where name='HelloWorldContract')
begin
drop contract HelloWorldContract;
end
go
create contract [HelloWorldContract]
(
[HelloWorldRequest] sent by initiator
,[HelloWorldRespone] sent by target
)
4.创建队列和基于队列的服务

--Queue
if object_id('dbo.HelloWorldTargerQueue') is not null and
exists(select top 1 1
    from sys.objects with (nolock)
    where object_id=object_id('dbo.HelloWorldTargerQueue')
     and type='SQ')
begin
drop queue dbo.HelloWorldTargerQueue
end
go
create queue dbo.[HelloWorldTargetQueue]

if exists (select top 1 1
    from sys.services with(nolock)
    where name='HelloWorldRequestService')
begin
drop service HelloWorldRequestService;
end
go
create service [HelloWorldRequestService] on queue [HelloWorldTargetQueue]
(
[HelloWorldContract]
)


if object_id('dbo.HelloWorldInitiatorQueue') is not null and
exists(select top 1 1
    from sys.objects with (nolock)
    where object_id=object_id('dbo.HelloWorldInitiatorQueue')
     and type='SQ'
    )
begin
drop queue dbo.HelloWorldInitiatorQueue
end
go
Create queue dbo.[HelloWorldInitiatorQueue]
if exists (select top 1 1
    from sys.services with(nolock)
    where name='HelloWorldResponseService')
begin
drop service HelloWorldResponseService;
end
go
create service [HelloWorldResponeService] on queue [HelloWorldInitiatorQueue]
(
[HelloWorldContract]
)

5.发送消息（Server Broker的消息发送，必须在事务中实现）

--send message
use ssbDemo
go
set nocount on
declare @conversionHandle uniqueidentifier
begin try
begin tran
--begin dialog to server
begin dialog @conversionHandle
from service [HelloWorldResponeService]
to service N'HelloWorldRequestService'
on contract [HelloWorldContract]
with encryption=off,lifetime=600;
--Send message
send on conversation @conversionHandle
message type [HelloWorldRequest] (N'Hello world')
commit
end try
begin catch
--get the the error info
select error_message()
end catch

6.接受消息（同样消息的接受也必须在事务中）

--receive message from raget queue
set nocount on
declare @conversionHandle uniqueidentifier
declare @message_body nvarchar(max)
declare @message_type_name sysname

--begin transaction
begin transaction
waitfor (receive top (1)--only a piece of message
@message_type_name = message_type_name--Receiving message type
,@conversionHandle=conversation_Handle--Dialog identifier
,@message_body=message_body
from [HelloWorldTargetQueue])

--it is a HelloWorldRequset
if @message_type_name=N'HelloWorldRequest'
begin
send on conversation @conversionHandle
message type HelloWorldRespone
(N'Hello from '+@@servername)
end conversation @conversionHandle
end
--commit
commit tran
go

7.检验

7.1检查目标队列

use ssbDemo
go
--check the target queue
select * from [HelloWorldTargetQueue] with (nolock)
--go
----convert the message to nvarchar
select cast(message_body as nvarchar(max))
from [HelloWorldTargetQueue]
go
7.2检查消息接受情况

--Check receiving whether sucessfully or fail
select cast(message_body as nvarchar(max))
from [HelloWorldTargetQueue]
7.3检查消息的反应情况

--Checking response message from initiator queue
select cast(message_body as nvarchar(max))
from [HelloWorldInitiatorQueue]