Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.
The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.
Here is what I'm trying to do:
;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @.Conversation_Handle
It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.
I've even tried this:
where cast(Conversation_Handle as varchar(100)) = cast(@.Conversation_Handle as varchar(100))
Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.
So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?
Thanks.
Trish wrote:
So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?
It does work, but a message with the conversation handle value (@.Conversation_Handle) has to exist in the queue in order to be RECEIVEd. It seems you are asking for a conversation handle that does not have any message ready to be received. You can look into queue using SELECT * FROM [ProcessingLetters] to see what messages are available for RECEIVE in the in the first place.
HTH,
~ Remus
When I do a SELECT * FROM ProcessingLetters it does have a record in it and it has the conversationhandle that I'm looking for.
|||Make sure that the conversation is the same, often is confusing that the initiator and target handle of the same conversation are sequential guids and look identical.
HTH,
~ Remus
568C2A9E-EE11-DB11-ABC7-00150042E356.
They sure look the same to me. But I'm becoming more convinced I'm just not doing something right.
|||One starts with 598, one starts with 568.|||Ok...here is a complete test senerio. It was working until I passed the conversation handle into a stored procedure (exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null). It will create everything and then drop everything when it's done. All I did was copy the code into a stored procedure and called the stored procedure...then it quit working.
use adventureworks
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'
)
DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration
GO
CREATE PROCEDURE dbo.SB_FinalizeQBForLetterGeneration
@.Result int,
@.ProcessingHandle uniqueidentifier,
@.FailureText nvarchar(256)
AS
DECLARE @.MsgBody XML
;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @.ProcessingHandle
print 'receiving letter from in processing'
if @.MsgBody is null
BEGIN
print 'conversation handle was not found - '+cast(@.ProcessingHandle as varchar(50))
END
ELSE
BEGIN
print 'conversation handle FOUND!!!! '
END
GO
CREATE MESSAGE TYPE SubmitQB VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE SubmitGrouping VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT QBLetterCreation (SubmitQB SENT BY INITIATOR, ProcessQB SENT BY INITIATOR, SubmitGrouping SENT BY INITIATOR)
CREATE QUEUE WaitingQBs WITH STATUS=ON
CREATE QUEUE ProcessingLetters WITH STATUS=ON
CREATE QUEUE WaitingToGroup WITH STATUS=ON
CREATE SERVICE QBWaiting ON QUEUE WaitingQBs (QBLetterCreation)
CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)
CREATE SERVICE LetterGrouping ON QUEUE WaitingToGroup (QBLetterCreation)
-- add to the WaitingQBs queue
BEGIN TRANSACTION
DECLARE @.conversationHandle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE QBWaiting
TO SERVICE 'QBWaiting'
ON CONTRACT QBLetterCreation
WITH ENCRYPTION=OFF;
-- Send a message on the conversation
DECLARE @.message nvarchar(max), @.xmlmsg XML
SET @.message = '<createletter><info QBGroupID="111111" Who="111" Why="jest testing" Retries="0"/></createletter>' ;
SET @.xmlmsg = CAST(@.message as XML)
;SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE SubmitQB(@.xmlmsg)
COMMIT TRANSACTION
--
END CONVERSATION @.conversationHandle
--
-- retrieve from waiting qbs
DECLARE @.Conversation_Handle_For_Waiting_QB uniqueidentifier;
DECLARE @.MsgBody XML
;RECEIVE TOP(1) @.MsgBody = CAST( message_body as XML ),
@.Conversation_Handle_For_Waiting_QB = conversation_handle
FROM WaitingQBs
IF @.Conversation_Handle_For_Waiting_QB is not null
BEGIN
-- end this conversation -- the initiator doesn't care so clean it up
print 'Ending the conversation for the waiting QB'
END CONVERSATION @.Conversation_Handle_For_Waiting_QB
DECLARE @.QBGroupID bigint,@.Who bigint, @.Why varchar(256), @.Retries int
SET @.QBGroupID = @.MsgBody.value('(/createletter/info/@.QBGroupID)[1]','bigint');
SET @.Who = @.MsgBody.value('(/createletter/info/@.Who)[1]','bigint');
SET @.Why = @.MsgBody.value('(/createletter/info/@.Why)[1]','nvarchar(256)');
SET @.Retries = @.MsgBody.value('(/createletter/info/@.Retries)[1]','int');
print @.QBGroupID
print @.Who
print @.Why
print @.Retries
-- now submit this QB to processing
-- first check to see if this qbgroupid is already in the queue
select message_body
from ProcessingLetters
WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = @.QBGroupID
IF @.@.ROWCOUNT = 0
BEGIN
-- adding to processing queue
BEGIN TRANSACTION
DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;
-- 300 = 300 seconds = 5 minutes
BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing
FROM SERVICE QBLetterProcessing
TO SERVICE 'QBLetterProcessing'
ON CONTRACT QBLetterCreation
WITH ENCRYPTION=OFF, LIFETIME = 300;
-- Send a message on the conversation
--DECLARE @.message nvarchar(max), @.xmlmsg XML
SET @.message = '<createletter><info QBGroupID="'+cast(@.QBGroupID AS nvarchar(20))+'" Who="'+cast(@.Who AS nvarchar(20))+'" Why="'+@.Why+'" Retries="'+cast(@.Retries as nvarchar(3))+'" Starttime="'+cast(getdate() as varchar(30))+'" /></createletter>' ;
SET @.xmlmsg = CAST(@.message as XML)
;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)
COMMIT TRANSACTION
print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))
--
-- we don't want to end the conversation...it stays open ..
-- it will end it after processing is done...properly
--
END
ELSE
BEGIN
print 'this qb group is already in processing letters'
END
END
ELSE
BEGIN
print 'getting next qb to process...no conversation handle'
END
-- now retrieve from the QBLetterProcess Queue
exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null
DROP SERVICE QBWaiting
DROP SERVICE QBLetterProcessing
DROP SERVICE LetterGrouping
DROP QUEUE WaitingQBs
DROP QUEUE ProcessingLetters
DROP QUEUE WaitingToGroup
DROP CONTRACT QBLetterCreation
DROP MESSAGE TYPE SubmitQB
DROP MESSAGE TYPE ProcessQB
DROP MESSAGE TYPE SubmitGrouping
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'
)
DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration
GO
|||Remus,
Did you run the script? Did you get the same results?
|||Remus,
So what I'm seeing is that my @.Conversation_Handle_To_Start_Processing is not what is actually stored in the queue as the conversation_handle. So how can I point back to conversation?
CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT QBLetterCreation (ProcessQB SENT BY INITIATOR)
CREATE QUEUE ProcessingLetters WITH STATUS=ON
CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)
-- adding to processing queue
BEGIN TRANSACTION
DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;
-- 300 = 300 seconds = 5 minutes
BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing
FROM SERVICE QBLetterProcessing
TO SERVICE 'QBLetterProcessing'
ON CONTRACT QBLetterCreation
WITH ENCRYPTION=OFF, LIFETIME = 300;
-- Send a message on the conversation
DECLARE @.message nvarchar(max), @.xmlmsg XML
SET @.message = '<createletter><info QBGroupID="11111" Who="111" Why="test" Retries="0" /></createletter>' ;
SET @.xmlmsg = CAST(@.message as XML)
;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)
COMMIT TRANSACTION
print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))
--
--
DECLARE @.MsgBody2 nvarchar(max)
;RECEIVE TOP(1) @.MsgBody2 = CAST(message_body as nvarchar(max))
FROM ProcessingLetters
WHERE conversation_handle = @.Conversation_Handle_To_Start_Processing
print 'receiving letter from in processing'
if @.MsgBody2 is null
BEGIN
DECLARE @.c uniqueidentifier
print 'conversation handle was not found - '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))
select @.c = conversation_handle from ProcessingLetters
print 'in table: '+cast(@.c as varchar(200))
select * from ProcessingLetters
END
ELSE
BEGIN
print 'conversation handle FOUND!!!! '
print @.MsgBody2
END
DROP SERVICE QBLetterProcessing
DROP QUEUE ProcessingLetters
DROP CONTRACT QBLetterCreation
DROP MESSAGE TYPE ProcessQB
|||I can't believe that you BEGIN a conversation by giving it a conversation handle and that is not what is stored in the queue(table) as the conversation_handle!!!! Why the heck then do you have to specifiy a conversation handle if it is never going to be used? And if you ARE going to do it that way, then have the SEND tell us what it created as a conversation handle!
So this is what I had to do.
SELECT @.Conversation_Handle_To_Start_Processing = conversation_handle
FROM ProcessingLetters
WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = '11111'
Then I could do my RECEIVE. You have to make sure you have a unique identifier in your message body if you want to do this. Otherwise you have no way of knowing what the conversation handle is.
I think the documentation should say that the conversation handle you send in is not the actual conversation handle that is stored.
|||A conversation consists from two endpoints, initiator and target, each with its own handle. The initiator endpoint is the one returned by the BEGIN DIALOG. The target endpoint is created by the first message arriving at the target service. Looking into sys.conversation_endpoints will show these two endpoints. The two endpoints belongig to the same conversation will have same value for conversation_id. BOL describes this here: http://msdn2.microsoft.com/en-us/library/ms166083.aspx
In your example, you are sending on the initiator's handle and trying to receive on the same handle. The initiator has no messages to availabe to be received, the message on the queue belongs to the target (since it was sent by initiator to target). You would have to receive this message (using the target's handle) and send back a reply to the initiator. Then the initiator would have a message available to be received.
HTH,
~ Remus
No comments:
Post a Comment