-- 远程服务器配置第三步:创建队列,服务,对话和路由给本地服务。 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)) 上一页 [1] [2]
|