January 21, 2011 at 8:45 am
I'm trying to take a stab at learning how the service broker works. I need to have a process running and it's doing what I want it to do. That is, update some records asynchronously. So far so good!;) The issue I'm having is that once it processes I still have records in sys.conversation_endpoints with a state of CONVERSING. Here's the activation SP that I'm using:
create proc [dbo].[updateConsumerDataSB]
as
begin
--set nocount on
declare @h uniqueidentifier
declare @t sysname
declare @m xml
while 1=1
begin
set @h = null
waitfor
(
receive top(1)
@h = conversation_handle,
@t = message_type_name,
@m = cast(message_body as xml)
from ReceiverAddressQ
--from SenderAddressQ
), timeout 5000
if @h is null
begin
break
end
else if @t = 'AddressMessage'
begin
exec updateConsumerData @m
end conversation @h
end
else if @t = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
begin
end conversation @h
end
else
begin
end conversation @h --don't know if I really want to do this
--Probably need to get out of SP
end
end
end
I think it has something to do with how I end the conversation I just don't know where I should be doing it.
Any thoughts?
January 21, 2011 at 3:10 pm
I was finally able to make it work but something tells me that it's not the correct way of doing it.
select @i = conversation_handle
from sys.conversation_endpoints ce
join (select conversation_id
from sys.conversation_endpoints
where conversation_group_id = @g) t on
ce.conversation_id = t.conversation_id and
ce.is_initiator = 1
--End the target and initiator conversations
end conversation @h
end conversation @i
I added the above code. I'm just using the conversation_group_id from the when I pulled the message and using that to grab the initiator conversation_handle. It works but I've got a long way before I can use this in a production environment. I still need to make this a transaction so I can roll back in the event there is a problem.
I only wish I would have started working with the service broker earlier. This is really cool stuff.
J.D.
January 25, 2011 at 7:40 am
If your doing a fire and forget[send and expect no response], once you have finished the delivery of your service broker messages just end the dialog at the end of that cycle.
If your looking for a response then you will need to create another activation proc to handle a delivery receipt message and close the conversation off there and then.
January 25, 2011 at 11:49 am
You have to end the conversation at both ends (initiator and target). Both should listen to and process messages of type "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" and "http://schemas.microsoft.com/SQL/ServiceBroker/Error"
When one of the participants in SB calls END CONVERSATION, a message of type "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" or "http://schemas.microsoft.com/SQL/ServiceBroker/Error" (dependig on the parameters used in END CONVERSATION) will be sent to the other participant. The response to this message should be END CONVERSATION.
From BOL, topic "END CONVERSATION":
Both participants in a conversation must call END CONVERSATION for the conversation to complete.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply