У меня есть брокер службы на одном компьютере, настроенный с использованием внутренней активации, брокер обрабатывает элементы, помещенные в очередь, и, по-видимому, корректно закрывает диалоги (см. сведения о профиле ниже).
Единственная запись, оставленная в sys.conversation_endpoints в состоянии CD — CLOSED, с отметкой security_timestamp, установленной через 30 минут в будущем, но эта запись никогда не удаляется.
У меня также есть собственный журнал, показывающий, что происходит. Единственное, что кажется странным, это то, что процедура инициатора вызывается с нулевыми значениями для talk_handle, message_type и message_body (последний элемент в моей информации журнала)
Я включил скрипт (имя изменено, не указывая на фактические имена), который создаст все объекты, которые я использую, и для имитации того, как я их использую, просто выполните:
dbo.usp_QueueRequest 2, null, 'Trial'
Это должно дать вам те же результаты, которые я постоянно видел
След:
EventClass TextData SPID Event Sequence EventSubClass
Broker:Activation 30 1394 1 - Start
Broker:Conversation CONVERSING 30 1395 1 - SEND Message
Broker:Message Classify 30 1396 1 - Local
Broker:Conversation CONVERSING 30 1397 6 - Received Sequenced Message
Broker:Activation 31 1398 1 - Start
Broker:Conversation DISCONNECTED_OUTBOUND 31 1399 2 - END CONVERSATION
Broker:Conversation Group 31 1400 2 - Drop
Broker:Message Classify 31 1401 1 - Local
Broker:Conversation DISCONNECTED_INBOUND 31 1402 7 - Received END CONVERSATION
Broker:Conversation CLOSED 31 1403 10 - Received END CONVERSATION Ack
Broker:Conversation CLOSED 30 1404 2 - END CONVERSATION
Broker:Conversation Group 30 1405 2 - Drop
Broker:Activation 30 1406 2 - Ended
Broker:Activation 31 1407 2 - Ended
Мой журнал
Sequence Description
1 Target activation for message type: RequestMessage
2 Call fake processing procedure
3 In fake processing procedure
4 Send ReplyMessage for conversation 8B355311-5D9F-E111-93EC-A4BADBFBB13B
5 Initiator activation
6 Initiator conversation 88355311-5D9F-E111-93EC-A4BADBFBB13B message type: ReplyMessage
7 Initiator received reply from target, end converstion
8 Initiator activation
9 Target activation for message type: http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
10 Target received end dialog
11 Initiator No conversation message type:No message type
Запись sys.converstation_endpoints (сокращенная)
conversation_handle is_initiator state state_desc far_service security_timestamp dialog_timer
8B355311-5D9F-E111-93EC-A4BADBFBB13B 0 CD CLOSED InitiatorService 2012-05-16 14:13:38.923 1900-01-01 00:00:00.000
Скрипт для создания объектов (имена изменены, а не настоящие имена)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Log]') AND type in (N'U'))
DROP TABLE [dbo].[Log]
GO
CREATE TABLE [dbo].[Log](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](max) NULL,
[DateTime] [date] NULL DEFAULT (getdate()))
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BrokerUser')
DROP USER [BrokerUser]
GO
CREATE USER [BrokerUser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'RequestMessage')
CREATE MESSAGE TYPE [RequestMessage] VALIDATION = WELL_FORMED_XML
IF NOT EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'AReplyMessage')
CREATE MESSAGE TYPE [ReplyMessage] VALIDATION = WELL_FORMED_XML
GO
IF NOT EXISTS (SELECT * FROM sys.service_contracts WHERE name =N'MessageContract')
CREATE CONTRACT [MessageContract]
([RequestMessage] SENT BY INITIATOR,
[ReplyMessage] SENT BY TARGET)
GO
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
CREATE QUEUE TargetQueue
GO
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
GRANT RECEIVE ON [TargetQueue] TO [BrokerUser]
GO
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService')
CREATE SERVICE [TargetService]
ON QUEUE TargetQueue
([MessageContract])
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'TargetService')
GRANT SEND ON SERVICE::[TargetService] TO [BrokerUser]
GO
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
CREATE QUEUE InitiatorQueue;
GO
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
GRANT RECEIVE ON [InitiatorQueue] TO [BrokerUser]
GO
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService')
CREATE SERVICE [InitiatorService]
ON QUEUE InitiatorQueue
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'InitiatorService')
GRANT SEND ON SERVICE::[InitiatorService] TO [BrokerUser]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FakeProcessing]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_FakeProcessing]
GO
CREATE PROCEDURE [dbo].[usp_FakeProcessing]
AS
BEGIN
insert [Log] ([Description])
VALUES ('In fake processing procedure');
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TargetActivation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_TargetActivation]
GO
CREATE PROCEDURE [dbo].[usp_TargetActivation]
AS
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @Message XML;
DECLARE @MessageType sysname;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@Handle = conversation_handle,
@Message = message_body,
@MessageType = message_type_name
FROM TargetQueue
), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
INSERT [Log] ([Description]) VALUES ('Target activation for message type: ' + + COALESCE(@MessageType,'No message type'))
-- it's an error message (some error occurred on the initiator side).
--This type of message is automatically created by SB when an END CONVERSATION command is executed with the WITH ERROR clause for the initiator
IF @MessageType =N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
ROLLBACK TRANSACTION; -- roll back the message transaction. In this example there was nothing done to roll back but it is good practice
-- in the event of an error
INSERT [Log] ([Description]) VALUES ('Error occured with SB')
END CONVERSATION @Handle; -- and end the conversation (dialog) on this side (target)
BREAK; -- break out of the loop (and end the stored proc)
END
ELSE IF @MessageType = N'RequestMessage'
BEGIN
INSERT [Log] ([Description]) VALUES ('Call fake procedure')
EXEC [usp_FakeProcessing];
INSERT [Log] ([Description]) VALUES ('Add reply message for conversation ' + CAST(@Handle AS varchar(80)) + ' with message : ' + CAST(@Message as VARCHAR(4000)));
SEND ON CONVERSATION @Handle
MESSAGE TYPE [ReplyMessage] (@message)
END
ELSE IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
INSERT [Log] ([Description]) VALUES ('Target received end dialog')
END CONVERSATION @Handle;
END
COMMIT TRANSACTION;
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InitiatorActivation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_InitiatorActivation]
GO
CREATE PROCEDURE [dbo].[usp_InitiatorActivation]
AS
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @Message XML;
DECLARE @MessageType sysname;
BEGIN
INSERT [Log] ([Description]) VALUES ('Initiator activation')
WAITFOR
( RECEIVE TOP(1)
@Handle = conversation_handle,
@Message = message_body,
@MessageType = message_type_name
FROM InitiatorQueue
), TIMEOUT 5000;
INSERT [Log] ([Description]) VALUES ('Initiator ' + ISNULL(CAST(@Handle AS VARCHAR(100)) , 'No handle') + ' message type:' + COALESCE(@MessageType,'No message type') + ' Body: ' + COALESCE(CAST(@Message AS varchar(1000)),'No body'))
IF @Handle IS NOT NULL
BEGIN
IF @MessageType = N'ReplyMessage'
BEGIN
INSERT [Log] ([Description]) VALUES ('Initiator received reply from target, end converstion')
END CONVERSATION @Handle;
END
IF @MessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
INSERT [Log] ([Description]) VALUES ('Initiator received end dialog message')
END CONVERSATION @Handle;
END
END
END
GO
GRANT EXECUTE ON [usp_TargetActivation] to [BrokerUser]
GO
GRANT EXECUTE ON [usp_InitiatorActivation] to [BrokerUser]
GO
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TargetQueue')
ALTER QUEUE TargetQueue
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = [usp_TargetActivation],
MAX_QUEUE_READERS = 1,
EXECUTE AS 'BrokerUser'
);
GO
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'InitiatorQueue')
ALTER QUEUE InitiatorQueue
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = [usp_InitiatorActivation],
MAX_QUEUE_READERS = 1,
EXECUTE AS 'BrokerUser'
);
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_QueueRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_QueueRequest]
GO
CREATE PROCEDURE [dbo].[usp_QueueRequest]
@numericvalue INT,
@StartDate DATE,
@TriggeredBy VARCHAR(50)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
DECLARE @MessageBody XML
DEClare @Params TABLE
(
ReadingDate SMALLDATETIME,
SiteNumber INT,
TriggeredBy VARCHAR(50)
)
INSERT INTO @Params(ReadingDate,SiteNumber, TriggeredBy)
VALUES(@StartDate, @numericvalue, @TriggeredBy)
SELECT @MessageBody = (SELECT * FROM @Params FOR XML PATH ('Params'), TYPE);
BEGIN TRANSACTION;
BEGIN DIALOG @Handle
FROM SERVICE [InitiatorService]
TO SERVICE 'TargetService'
ON CONTRACT [MessageContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE
[RequestMessage]
(@MessageBody);
COMMIT TRANSACTION;
END
GO
