-- 远程服务器配置第三步：创建队列，服务，对话和路由给本地服务。
USE XueitDB;
GO
-- 启用Service Broker
ALTER DATABASE XueitDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE XueitDB set ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE XueitDB SET TRUSTWORTHY ON
GO
-- 创建消息接收处理过程
CREATE PROCEDURE PRO_ReceiveMessage
AS
BEGIN
SET NOCOUNT ON;
DECLARE @message_body varbinary(MAX)
DECLARE @message_type int
DECLARE @dialog uniqueidentifier
while (1 = 1)
begin
begin transaction
-- Wait for the message.
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM RemotServiceQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
-- Check for the End Dialog message.
If (@message_type <> 2) -- End dialog message
BEGIN
--PRINT '接收的消息为: ' + @message_body
--SELECT [dbo].[SendRtxNotify] ('Title', cast(@message_body varchar), '周宏艳', 0)
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[SSBTest]') AND TYPE IN ( N'U' ))
DROP TABLE SSBTest
SELECT @dialog dialog,@message_body message_body,@message_type message_type INTO SSBTest
END
-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue and the response
-- wouldn't be sent.
commit transaction
end
END
GO
-- Create a message queue.
CREATE QUEUE RemotServiceQueue WITH STATUS = ON , RETENTION = OFF ,ACTIVATION (
STATUS = ON
,PROCEDURE_NAME = dbo.PRO_ReceiveMessage
,MAX_QUEUE_READERS = 10
,EXECUTE AS SELF
);
GO
-- Create a service with a default contract.
CREATE SERVICE RemotServiceService ON QUEUE RemotServiceQueue ([DEFAULT]);
GO
-- Any user can send on the service.
GRANT SEND ON SERVICE::RemotServiceService TO PUBLIC;
GO
-- Create a route to the initiator service.
CREATE ROUTE LocalServiceRoute
WITH SERVICE_NAME = 'LocalServiceService',
ADDRESS = 'tcp://127.0.0.1:4022';
GO
-- In msdb, create an incoming route to the target service.
USE msdb;
GO
CREATE ROUTE RemotServiceRoute
WITH SERVICE_NAME = 'RemotServiceService',
ADDRESS = 'local';
GO
-- The target creates a database, queue, service, and routes for the dialog
-- to the initiator service.
-- Modify the initiator_host name in script to suit configuration.
-- 本地服务器配置第四步：本地发送消息测试
USE TestDB;
GO
-- Create a message.
DECLARE @message varchar(max);
SELECT @message = '测试发送消息';
-- Create a dialog to the target service.
-- Note: here the dialog does not encrypt; the endpoint does.
DECLARE @handle uniqueidentifier;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE LocalServiceService
TO SERVICE 'RemotServiceService'
WITH ENCRYPTION = OFF;
-- Send the message.
SEND ON CONVERSATION @handle (@message);
PRINT '发送消息会话:';
PRINT @handle;
GO
-- The initiator creates a dialog and sends a message to the target service.
------------------------------------------------------------------------------------
------------------ 基本同一服务器及数据库发送和接收Service Broker消息------------------
------------------------------------------------------------------------------------
CREATE PROCEDURE PRO_SSBAddResource
AS
declare @message_body nvarchar(MAX)
declare @message_type int
declare @dialog uniqueidentifier
while (1 = 1)
begin
begin transaction
-- Receive the next available message from the queue
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM ResourceQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
-- Check for the End Dialog message.
If (@message_type <> 2) -- End dialog message
BEGIN
-- Send the message back to the sender.
SEND ON CONVERSATION @dialog -- send it back on the dialog we received the message on
MESSAGE TYPE ResourceResponseMessage -- Must always supply a message type
(@message_body); -- the message contents (a varbinary(MAX) blob
END
commit transaction
end
go
-- Create the required meta-data
CREATE MESSAGE TYPE ResourceRequestMessage VALIDATION = NONE
CREATE MESSAGE TYPE ResourceResponseMessage VALIDATION = NONE
CREATE CONTRACT ResourceContract
( ResourceRequestMessage SENT BY INITIATOR,
ResourceResponseMessage SENT BY TARGET)
-- 启用Service Broker
ALTER DATABASE DTEL SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE DTEL set ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DTEL SET TRUSTWORTHY ON
GO
------------------------------------------
CREATE QUEUE test_queue
--WITH ACTIVATION (
-- PROCEDURE_NAME = [<ssb-proc-name, sysname, test_proc>] ,
-- MAX_QUEUE_READERS = 5,
-- EXECUTE AS SELF )
------------------------------------------
CREATE SERVICE test_service
ON QUEUE test_queue([DEFAULT])
------------------------------------------
CREATE ROUTE test_route
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'test_service',
ADDRESS = N'local'
------------------------------------------
DECLARE @handle uniqueidentifier;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE test_service
TO SERVICE 'test_service';
-- Send the message.
SEND ON CONVERSATION @handle ('ccc');
------------------------------------------
select * from dbo.test_queue
select * from sys.transmission_queue
------------------------------------------
declare @message_body varbinary(MAX)
declare @message_type int
declare @dialog uniqueidentifier
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.test_queue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
print cast(@message_body as varchar(max))
|
