Newbie – 2 way dialog and queues on same database
GiJeet
loan application system. We have loan takers that submit the customer
info another party, a reviewer to review the application and they
either pass or fail the applicant. However, the review may require
some additional info that loan taker did not provide so they need to
send a message back to the taker requesting additional info, then the
taker re-sends the info back to the reviewer. – all in the same
database.
So should I setup 4 queues:
CREATE QUEUE [dbo].[TakerInitiatorQueue];
CREATE QUEUE [dbo].[ReviewTargetQueue];
CREATE QUEUE [dbo].[ReviewInitiatorQueue];
CREATE QUEUE [dbo].[TakerTargetQueue];
so the taker always has a queue to initiate sending applicant as the
first process and the Reviewer always has a queue to look in. Then
the Reviewer has a queue to initiate a resend and the Taker always has
a queue to look for requests for more info?
Or can you use a single queue and how would that work? What’s to
prevent the taker from pulling the message he/she just inserted into
the queue to be sent to the Reviewer? In other words, if they insert
into a message to the queue to be send to the reviewer and immediately
check the queue for any message for them, unless there is a way to
filter what they can pull they will pull the message they just
inserted.
TIA
gi
Dan Guzman
You need 2 queues, one for your Taker service and the other for the other
for the reviewer service. The bi-directional conversion can continue as
long as needed. Below is an example of the conversation flow between your 2
services.
--taker starts conversation and sends initial loan information
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE LoanTakerService
TO SERVICE 'LoanReviewerService'
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversation_handle
('<ApplicationInformation><ApplicantName>John
Doe</ApplicantName></ApplicationInformation>');
GO
--Reviewer: get initial loan information and request more information
RECEIVE
CAST(message_body AS XML),
conversation_handle FROM dbo.LoanReviewerQueue;
SEND ON CONVERSATION @conversation_handle
('<InformationRequest>Need Applicant Address</InformationRequest>');
GO
--Taker: get request for more information and send it back to reviewer
RECEIVE
CAST(message_body AS XML),
conversation_handle
FROM dbo.LoanTakerQueue;
SEND ON CONVERSATION @conversation_handle
('<ApplicationInformationInformation><ApplicantAddress>123 Main
Street</ApplicantAddress></ApplicationInformation>');
GO
--Reviewer: get additional information, send final loan status and end
conversation
RECEIVE
CAST(message_body AS XML),
conversation_handle FROM dbo.LoanReviewerQueue;
SEND ON CONVERSATION @conversation_handle
('<ApplicationStatus>Successfully Processed</ApplicationStatus>');
END CONVERSATION @conversation_handle;
GO
--Taker: get the successful and end-dialog messages and end conversation
RECEIVE
CAST(message_body AS XML),
conversation_handle
FROM dbo.LoanTakerQueue;
END CONVERSATION @conversation_handle;
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GiJeet" <gij...@yahoo.com> wrote in message
news:d0d15e62-6dbd-4824...@e6g2000vbe.googlegroups.com...
GiJeet
>>The bi-directional conversion can continue as long as needed.
Thanks Dan for responding, I really appreciate this as I’m trying to
get my head around this new service.
So the two way conversation uses the same @conversation_handle? Then
that means this value must be persisted somewhere AND has to be
associated with this specific conversation. That is, I can’t use this
@conversation_handle for a different loan or things will get screwed
up.
Don’t forget, the Reviewer may not send his request for more
information until days later, it’s not going to be immediate.
A few questions:
1) do I need to write custom code to handle the persistence of this
@conversation_handle somewhere? And if so, how would you do it?
2) How is this @conversation_handle associated with this specific
conversation? That is, how do I know this @conversation_handle is for
loan application A100? Do I have to create a user table with say a
column for the @conversation_handle and a column for the Loan
Application ID so that it links the two together?
3) Also, when you say 2 queues, each side has a queue where they put
messages they want to SEND and each has a queue where the check for
messages RECEIVED?
Also can you suggest a good book or website that I can study from?
TIA
GiJeet
Also, since each side will be sending messages and receiving messages
I assume I need to create two services and two stored procs for each
side?
Here is an example of the queues code:
/*create taker queue*/
CREATE QUEUE [dbo].[TakerQ]
WITH ACTIVATION (
STATUS = OFF,
PROCEDURE_NAME = dbo.TakerSender_sp,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF
);
/*create reviewer queue*/
CREATE QUEUE [dbo].[ReviewerQ]
WITH ACTIVATION (
STATUS = OFF,
PROCEDURE_NAME = dbo.ReviewerReceiver_sp,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF
);
NOTE - that there is only one stored proc named for each queue. I
need to name two procs for each queue right?
E.g.a dbo.TakerSender_sp to send the msg to the Reviewer and I also
need a dbo.TakerReceiver_sp that will receive/process the messages
coming back from the Reviewer.
Can I associate two procs per queue or should I just not assign a
stored proc when creating the queue?
Dan Guzman
My apologies if my script example caused confusion. The
@conversation_handle variable in the script represents the
conversation_handle value retrieved from the previous RECEIVE. Each side
has a different conversation_handle value and the value is unique to each
side.
> Don’t forget, the Reviewer may not send his request for more
> information until days later, it’s not going to be immediate.
That's fine. The default lifetime in seconds is the max int value of
2,147,483,647, which is about 68 years.
> 1) do I need to write custom code to handle the persistence of this
> @conversation_handle somewhere? And if so, how would you do it?
>
> 2) How is this @conversation_handle associated with this specific
> conversation? That is, how do I know this @conversation_handle is for
> loan application A100? Do I have to create a user table with say a
> column for the @conversation_handle and a column for the Loan
> Application ID so that it links the two together?
The conversation handle is stored in the queue along with the message so you
don't need to store it (or the conversation_group_id) separately unless you
need to maintain an application state table. In your case, you would
probably use such a state table, keyed for reverse lookups of loan
application id.
> 3) Also, when you say 2 queues, each side has a queue where they put
> messages they want to SEND and each has a queue where the check for
> messages RECEIVED?
Each conversation side has a queue for messages RECEIVEd. The queue for
SEND messages is abstracted via the service and is really the other
conversation side's queue for RECEIVEd messages. So each side SENDs to the
other side's RECEIVE queue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GiJeet" <gij...@yahoo.com> wrote in message
news:d6d333dc-4f0a-4d6c...@v38g2000yqb.googlegroups.com...
GiJeet
online.sbcglobal.net> wrote:
> > So the two way conversation uses the same @conversation_handle?
>
> My apologies if my script example caused confusion. The
> @conversation_handle variable in the script represents the
> conversation_handle value retrieved from the previous RECEIVE. Each side
> has a different conversation_handle value and the value is unique to each
> side.
well, if I understand you correctly, then the value of
@conversation_handle will ALWAYS be the same. That is, when the taker
sends a msg to the reviewer the reviewer opens their received msg with
say @conversation_handle 1. They then send back a message to the
taker using @conversation_handle 1 and the taker opens their received
msg with @conversation_handle 1. When they resend back to the
reviewer, they use @conversation_handle 1 again and this number gets
passed back and forth. Is this correct or am I completely off
base… :) if you could explain it in a little more detail I’d be very
appreciative.
Dan Guzman
> @conversation_handle will ALWAYS be the same. That is, when the taker
> sends a msg to the reviewer the reviewer opens their received msg with
> say @conversation_handle 1. They then send back a message to the
> taker using @conversation_handle 1 and the taker opens their received
> msg with @conversation_handle 1. When they resend back to the
> reviewer, they use @conversation_handle 1 again and this number gets
> passed back and forth. Is this correct or am I completely off
> base… :) if you could explain it in a little more detail I’d be very
> appreciative.
Each conversation side has a different conversation handle value that is
maintained for the life of the conversation. Below is a complete script
that illustrates this. Note that the Taker and Reviewer handles are
different but the values do not change during the conversation. I hope this
makes things clearer.
--create Service Broker Objects
CREATE QUEUE dbo.LoanTakerQueue;
CREATE SERVICE LoanTakerService
ON QUEUE dbo.LoanTakerQueue;
CREATE QUEUE dbo.LoanReviewerQueue;
CREATE SERVICE LoanReviewerService
ON QUEUE dbo.LoanReviewerQueue
([DEFAULT]);;
GO
--taker starts conversation and sends initial loan information
DECLARE @conversation_handle uniqueidentifier;
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE LoanTakerService
TO SERVICE 'LoanReviewerService'
WITH ENCRYPTION = OFF;
SELECT @conversation_handle AS conversation_handle;
SEND ON CONVERSATION @conversation_handle
('<ApplicationInformation><ApplicantName>John
Doe</ApplicantName></ApplicationInformation>');
GO
--Reviewer: get initial loan information and requests more information
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle
FROM dbo.LoanReviewerQueue;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
SEND ON CONVERSATION @conversation_handle
('<InformationRequest>Need Applicant Address</InformationRequest>');
GO
--Taker: gets request for more information and sends it back to reviewer
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle
FROM dbo.LoanTakerQueue;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
SEND ON CONVERSATION @conversation_handle
('<ApplicationInformation><ApplicantAddress>123 Main
Street</ApplicantAddress></ApplicationInformation>');
GO
--Reviewer: get additional information, sends final status and ends
conversation
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle
FROM dbo.LoanReviewerQueue;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
SEND ON CONVERSATION @conversation_handle
('<ApplicationStatus>Successfully Processed</ApplicationStatus>');
END CONVERSATION @conversation_handle
GO
--Taker: gets the successful and end-dialog messages and ends conversation
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
RECEIVE TOP (1)
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle
FROM dbo.LoanTakerQueue;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
RECEIVE TOP (1)
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name,
@conversation_handle = conversation_handle
FROM dbo.LoanTakerQueue;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
END CONVERSATION @conversation_handle;
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GiJeet" <gij...@yahoo.com> wrote in message
news:7ae1aac5-20e4-4203...@v31g2000vbb.googlegroups.com...
Dan Guzman
> need to name two procs for each queue right?
> E.g.a dbo.TakerSender_sp to send the msg to the Reviewer and I also
> need a dbo.TakerReceiver_sp that will receive/process the messages
> coming back from the Reviewer.
>
> Can I associate two procs per queue or should I just not assign a
> stored proc when creating the queue?
You need zero or one activated proc per queue. An activated proc is
launched automatically when a messages are on the queue waiting to be
received. After receiving a message, an activated proc can end the
conversation, send a message back to the initiator service or begin a
conversation with another service.
The purpose of an activated proc on the target queue is to implement the
target service. An activated proc on the initiator side is commonly used to
log errors and end the conversation to close the loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GiJeet" <gij...@yahoo.com> wrote in message
news:69d618be-b498-44a4...@e24g2000vbe.googlegroups.com...
Tibor Karaszi
I'd go for this one:
http://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270/ref=sr_1_2?ie=UTF8&s=books&qid=1234944131&sr=8-2
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"GiJeet" <gij...@yahoo.com> wrote in message
news:d6d333dc-4f0a-4d6c...@v38g2000yqb.googlegroups.com...
GiJeet
Dan, thanks for this script. I just a little confused as to when I
should issue the END CONVERSATION command. In you script above, it
looks like the Reviewer should end the conversation since the Taker
will always be awaiting a response from the Reviewer - either a
request for more info or a success/failed status. Then I assume the
Taker would start a new conversation each time they send a message to
the Reviewer. Also, on the last step it looks like the END
CONVERSATION @conversation_handle; was issued twice - once by the
Reviewer then again by the Taker when they received the final status.
Is that required? If the Reviewer already ended the conversation does
the Taker need to do it also? Does that command mean the conversation
handle is dead and can't be used again?
Dan Guzman
> should issue the END CONVERSATION command. In you script above, it
> looks like the Reviewer should end the conversation since the Taker
> will always be awaiting a response from the Reviewer - either a
> request for more info or a success/failed status.
The Taker service can receive 3 possible messages: user message requesting
additional information, an EndDialog message or an Error Message. The the
Taker should end the conversation only when EndDialog or Error is
encountered. In the case of an additional information request, the Taker
should not end the conversation but instead send back the requested info.
> Also, on the last step it looks like the END
> CONVERSATION @conversation_handle; was issued twice - once by the
> Reviewer then again by the Taker when they received the final status.
> Is that required? If the Reviewer already ended the conversation does
> the Taker need to do it also? Does that command mean the conversation
> handle is dead and can't be used again?
It is important than both conversation sides end the conversation to prevent
leaking of conversation handles. The basic pattern is that the initiator
(Taker in your case) ends the conversation last after an EndDialog or Error
message is received from the target (Reviewer in your case). The same
conversation can continue back and forth until the Reviewer completes the
application and notifies the taker via an optional status message followed
by END CONVERSATION. The Taker receives the resultant End Dialog message
and execute END CONVERSATION to close its side.
I think the book Tabor suggested will greatly help your understanding.
Service Broker is a different paradigm than database developers are
accustomed to and there are some basic design principals and patterns that
need to be followed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GiJeet" <gij...@yahoo.com> wrote in message
news:9a87ec7e-4fce-4136...@j1g2000yqi.googlegroups.com...