Service Broker跨数据库会话

第 1 步：创建数据库

--创建数据库并设置 TRUSTWORTHY 选项

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases

WHERE name = N'TargetDB')--目标方数据库

DROP DATABASE TargetDB;

GO

CREATE DATABASE TargetDB;

GO

IF EXISTS (SELECT * FROM sys.databases

WHERE name = N'InitiatorDB')--发起方数据库

DROP DATABASE InitiatorDB;

GO

CREATE DATABASE InitiatorDB;

GO

ALTER DATABASE InitiatorDB SET TRUSTWORTHY ON;

GO

第 2 步：创建目标会话对象

USE TargetDB;

GO

--创建消息类型

CREATE MESSAGE TYPE [//BothDB/2DBSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [//BothDB/2DBSample/ReplyMessage]

VALIDATION = WELL_FORMED_XML;

GO

--创建约定

CREATE CONTRACT [//BothDB/2DBSample/SimpleContract]

([//BothDB/2DBSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2DBSample/ReplyMessage]

SENT BY TARGET

);

GO

--创建目标队列和服务

CREATE QUEUE TargetQueue2DB;

CREATE SERVICE [//TgtDB/2DBSample/TargetService]

ON QUEUE TargetQueue2DB

([//BothDB/2DBSample/SimpleContract]);

GO

第 3 步：创建发起方会话对象

--切换到 InitiatorDB 数据库

USE InitiatorDB;

GO

--创建消息类型，消息类型名称和属性必须与第2步在 TargetDB 中创建的消息类型名称和属性相同

CREATE MESSAGE TYPE [//BothDB/2DBSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [//BothDB/2DBSample/ReplyMessage]

VALIDATION = WELL_FORMED_XML;

GO

--创建约定，约定名称和属性必须与第2步在 TargetDB 中创建的约定名称和属性相同

CREATE CONTRACT [//BothDB/2DBSample/SimpleContract]

([//BothDB/2DBSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2DBSample/ReplyMessage]

SENT BY TARGET

);

GO

--创建发起方队列和服务

CREATE QUEUE InitiatorQueue2DB;

CREATE SERVICE [//InitDB/2DBSample/InitiatorService]

ON QUEUE InitiatorQueue2DB;

GO--由于未指定约定名称，因而其他服务不可将此服务用作目标服务。

第 4 步：启动会话并传输消息

--启动一个跨越同一数据库引擎实例中的两个数据库的会话

--切换到 A_InitiatorDB 数据库

USE A_InitiatorDB;

GO

--启动会话并发送请求消息

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//InitDB/2DBSample/InitiatorService]

TO SERVICE N'//TgtDB/2DBSample/TargetService'

ON CONTRACT [//BothDB/2DBSample/SimpleContract]

WITH

ENCRYPTION = OFF;

SELECT @RequestMsg =

N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle

MESSAGE TYPE [//BothDB/2DBSample/RequestMessage]

(@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

GO