Hi Remus,
thank you very much for your answer...
Maybe it is not necessarely but this is the SP wuch start the conversation (activated by a trigger on a insert )
PROCEDURE [dbo].[sp_sendInserted]
@MessageXML nvarchar(max)
AS
BEGIN
DECLARE @dialog_handle uniqueidentifier;
DECLARE @dialog_id uniqueidentifier;
DECLARE @msg XML;
DECLARE @Error INT;
SET NOCOUNT OFF;
BEGIN TRY
BEGIN TRANSACTION;
set @msg = Convert(xml,@MessageXML);
WHILE (1=1)
BEGIN
set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceivedService');
if @dialog_handle is null
--Begin new dialog
BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SendService]
TO SERVICE 'ReceivedService'
ON CONTRACT [MainContract]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@dialog_handle) TIMEOUT = 600;
END;
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE MyMessage (@msg); END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
BEGIN
ROLLBACK TRANSACTION;
END
Insert into [dbo].[tblErrorXMLMessages] values(@MessageXML,@dialog_handle,@dialog_id,null,null,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_PROCEDURE(),getdate());
END CATCH
END
And this is the SP activated on the ReceiveQueue:
PROCEDURE [dbo].[OnReceivedMessage]
AS
DECLARE @message_type INT;
DECLARE @messagetypename NVARCHAR(1000);
DECLARE @XMLmessage_body XML;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @dialog_id UNIQUEIDENTIFIER;
DECLARE @ErrorSave INT;
DECLARE @ErrorDesc NVARCHAR(100);
SET NOCOUNT ON;
WHILE (1 = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
WAITFOR (
RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@messagetypename=message_type_name,
@XMLmessage_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [ReceivedQueue]
)--, timeout 5000; -- if the queue is empty for three second
--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages
set @dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @dialog);
-- If we didn't get anything, bail out
if (@@ROWCOUNT = 0)
BEGIN
INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');
COMMIT;
BREAK;
END
If (@messagetypeName = N'MyMessage')
BEGIN
DECLARE @ret integer;
EXECUTE @ret = [TestReceiver].[dbo].[sp_ShredXMLMessageToRelationalData] @XMLmessage_body; --SP that PROCESSES THE MESSAGE RECEIVED ON THE QUEUE
if (@ret <> 0)
INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES
(cast(@XMLmessage_body as nvarchar(max)),@dialog,@dialog_id,null,null,0,null,null,getdate());
END
ELSE IF (@messagetypeName = N'EndOfStream')
BEGIN
END CONVERSATION @dialog;
END
-- Check for the Error Dialog message.
ELSE IF(@messagetypeName=N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
DECLARE @WrongXMLMessage NVARCHAR(max);
set @WrongXMLMessage = (Select message_body FROM dbo.TradeReceivedQueue WITH (NOLOCK) where Message_type_name = 'MyMessage' and status = 3 and conversation_handle=@dialog);
DECLARE @Error int;
DECLARE @ErrorDescription nvarchar(4000);
WITH XMLNAMESPACES
('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb)
SELECT @Error = cast(@XMLMessage_Body as XML).value('(//ssb:Error/ssb:Code)[1]','INT'),
@ErrorDescription = cast(@XMLMessage_Body as XML).value('(//ssb:Error/ssb
escription)[1]','nvarchar(4000)')
IF @WrongXMLMessage is null
BEGIN
SET @WrongXMLMessage = N'<Error>Error retrieving the wrong XML message from the sender queue</Error>';
END
Insert into [TestSender].[dbo].[tblErrorXMLMessages] values(@WrongXMLMessage,@dialog,@dialog_id,'MArina','ReceivedQueue',@Error,@ErrorDescription,ERROR_PROCEDURE(),getdate());
--After the End Conversation all messages from the queue are deleted
END CONVERSATION @dialog;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
insert into [testReceiver].[dbo].[tblReceived] values('Error','<Marina>Error in SP Traget QUEUE</Marina>');
END CATCH
END
Let me know whether you need more info regarding contract, queues etc..etc..
Thank you very much!
Marina B.
marina B. wrote: