How to identify who message in queue is assigned to?

[GiJeet]

I have several parties that need to receive messages in a queue, ie,
share a queue. But via the structure of a queue, I don’t see a way to
say certain messages are for party A and other messages are for party
B. Is there a way to do this?

Thanks,
Gi

[Bob Beauchemin]

Hi GiJeet,

You'd need to send separate messages (one for each party) in separate
conversations for this to happen. Or have the first reader store the
information is a state table to be read by other "readers". It would
probably be "cleaner" to have separate queues/services and have the sender
send a message to each service. Or two have a service who's job it is to
"multiplex" messages. At this point in time, service broker has no built-in
way to broadcast a message to multiple recipients. If I'm understanding
correctly, this most corresponds to the concept of monologs which was not
implemented in service broker in SQL Server 2005/2008.

Here's an article that shows how to simulate monologs with broker:
http://www.codeproject.com/KB/reporting-services/SimulatingMonologConverse.aspx?display=Print.
There is a company named TwoConnect (http://www.twoconnect.com) that had an
implementation of monologs in broker once, but I don't currently see it on
their web site.

Hope this helps,
Bob Beauchemin
SQLskills

"GiJeet" <gij...@yahoo.com> wrote in message
news:11068af3-ed02-4ae3...@n21g2000vba.googlegroups.com...

[GiJeet]

>>On Feb 23, 11:18 am, "Bob Beauchemin" <no_bobb_s...@sqlskills.com> wrote:
>
> You'd need to send separate messages (one for each party) in separate
> conversations for this to happen. Or have the first reader store the
> information is a state table to be read by other "readers". It would
> probably be "cleaner" to have separate queues/services and have the sender
> send a message to each service. Or two have a service who's job it is to
> "multiplex" messages. At this point in time, service broker has no built-in
> way to broadcast a message to multiple recipients. If I'm understanding
> correctly, this most corresponds to the concept of monologs which was not
> implemented in service broker in SQL Server 2005/2008.
>

> Here's an article that shows how to simulate monologs with broker:http://www.codeproject.com/KB/reporting-services/SimulatingMonologCon....

> There is a company named TwoConnect (http://www.twoconnect.com) that had an
> implementation of monologs in broker once, but I don't currently see it on
> their web site.

Thanks.

Just to clarify. I have several stores that take loan applications
and all send to a single loan approval site, a single approval site is
responsible for several stores. So it’s a M:1 situation. That is, 10
stores will send to 1 approval site. Then another 10 stores to
another single approval site, etc.. I want the messages from the
stores to queue up in the approval site’s queue but I don’t want to
create 10 queues just for this single approval site if I don’t have
to. Why should this single approval site have to check 10 queues
right?

This seems the opposite of a multicast where a single store can SEND
to multiple queues. I want a single queue to RECEIVE from multiple
sites (queues). Is this possible in SQL2005?

[GiJeet]

>>On Feb 24, 7:32 am, GiJeet <gij...@yahoo.com> wrote:
As a follow up to my last post, to make an analogy: I need a queue to
behave like an email in-basket. With my email system I have a single
in-basket that can receive messages from many parties. I need to
setup a target queue that can receive from multiple initiator queues.
Is this possible ?

TIA
Gi

[Dan Guzman]

If I understand correctly, you still only need 2 queues, one for the Taker
and another for the Reviewer. Each taker instance (store) can specify a
conversation_handle in the RECEIVE WHERE clause so that only loans sent from
that that store are received.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"GiJeet" <gij...@yahoo.com> wrote in message

news:a68f0dfd-dc79-4f99...@h5g2000yqh.googlegroups.com...

[GiJeet]

On Feb 24, 8:45 am, "Dan Guzman" <guzma...@nospam-

online.sbcglobal.net> wrote:
>Each taker instance (store) can specify a
> conversation_handle in the RECEIVE WHERE clause so that only loans sent from
> that that store are received.

I'm sorry but you kinda lost me on this.

As I said before, I need one Target to be able to receive messages
from multiple Initiators. How to set this up, if possible?

With all the different objects: contracts, services, queues, etc. I’m
not sure how to set this type of architecture up.

I ordered the book from Amazon but by the time it gets to me and I
read it…the project will be basically over… :)

[Dan Guzman]

> As I said before, I need one Target to be able to receive messages
> from multiple Initiators. How to set this up, if possible?

A single target service can have multiple concurrent conversations going on
with different initiators. Each conversation will have a different
conversation_handle. You'll probably need a state table to track the
different states of the loan applications in progress.

Below is a slightly beefed up DDL example from the earler thread. I'm
running short on time now but I'll try to proved more detail on the
conversation flow later.

CREATE MESSAGE TYPE LoanApplicationInformation
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE LoanApplicationResponse
VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT LoanApplcation
AUTHORIZATION dbo
( LoanApplicationInformation SENT BY INITIATOR,
LoanApplicationResponse SENT BY TARGET );

CREATE QUEUE dbo.LoanTakerQueue;

CREATE SERVICE LoanTakerService
AUTHORIZATION dbo
ON QUEUE dbo.LoanTakerQueue
(LoanApplcation);

CREATE QUEUE dbo.LoanReviewerQueue;

CREATE SERVICE LoanReviewerService
AUTHORIZATION dbo
ON QUEUE dbo.LoanReviewerQueue
(LoanApplcation);
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"GiJeet" <gij...@yahoo.com> wrote in message

news:c665b816-8f9e-41fd...@v15g2000vbb.googlegroups.com...

[Dan Guzman]

Hi, GiJeet.

The example below shows a conversation flow between 2 stores and a single
target. In a real world implementation, a state table would probably be
used to track the in-progress loan applications for each store, including
the conversation handle that each store will need to recieve only its
messagess.

CREATE MESSAGE TYPE LoanApplicationInformation
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE LoanApplicationResponse
VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT LoanApplication

AUTHORIZATION dbo
( LoanApplicationInformation SENT BY INITIATOR,
LoanApplicationResponse SENT BY TARGET );

CREATE QUEUE dbo.LoanTakerQueue;

CREATE SERVICE LoanTakerService
AUTHORIZATION dbo
ON QUEUE dbo.LoanTakerQueue

(LoanApplication);

CREATE QUEUE dbo.LoanReviewerQueue;

CREATE SERVICE LoanReviewerService
AUTHORIZATION dbo
ON QUEUE dbo.LoanReviewerQueue

(LoanApplication);
GO

--store 0001 starts conversation and sends initial loan information
DECLARE @conversation_handle uniqueidentifier, @StateInformation xml
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE LoanTakerService
TO SERVICE 'LoanReviewerService'
ON CONTRACT LoanApplication
WITH ENCRYPTION = OFF;
SELECT @conversation_handle AS conversation_handle;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE LoanApplicationInformation
('<LoanApplicationInformation>
<InitiatingStoreID>0001</InitiatingStoreID>
<ApplicantName>John Doe</ApplicantName>
<LoanAmount>500</LoanAmount>
</LoanApplicationInformation>');
GO

--store 0002 starts conversation and sends initial loan information
DECLARE @conversation_handle uniqueidentifier, @StateInformation xml
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE LoanTakerService
TO SERVICE 'LoanReviewerService'
ON CONTRACT LoanApplication
WITH ENCRYPTION = OFF;
SELECT @conversation_handle AS conversation_handle;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE LoanApplicationInformation
('<LoanApplicationInformation>
<InitiatingStoreID>0002</InitiatingStoreID>
<ApplicantName>Jane Smith</ApplicantName>
<ApplicantAddress>123 Test Name</ApplicantAddress>
<LoanAmount>500</LoanAmount>
</LoanApplicationInformation>');
GO

--Reviewer: get loan application from store 0001 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
MESSAGE TYPE LoanApplicationResponse
('<LoanApplicationResponse>
<Status>Need Applicant Address</Status>
</LoanApplicationResponse>');
GO

--Reviewer: get loan application sent from store 0002 and approves
application
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
MESSAGE TYPE LoanApplicationResponse
('<LoanApplicationResponse>
<Status>Approved</Status>
</LoanApplicationResponse>');
END CONVERSATION @conversation_handle;
GO

--store 0001 gets request from more info and responds with address info
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
--note @conversation_handle value in WHERE clause is same as initial SEND
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name
FROM dbo.LoanTakerQueue
WHERE conversation_handle = @conversation_handle;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE LoanApplicationInformation
('<LoanApplicationInformation>
<ApplicantAddress>123 Main Street</ApplicantAddress>
</LoanApplicationInformation>');
GO

--store 0002 gets approved status and end dialog message and ends
conversation
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
--note @conversation_handle value in WHERE clause is same as initial SEND
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name
FROM dbo.LoanTakerQueue
WHERE conversation_handle = @conversation_handle;
SELECT @message_body message_body,
@conversation_handle,
@message_type_name;
END CONVERSATION @conversation_handle;
GO

--Reviewer: get additional information from store 0001, sends rejected
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
MESSAGE TYPE LoanApplicationResponse
('<LoanApplicationResponse>
<Status>Rejected</Status>
</LoanApplicationResponse>');
END CONVERSATION @conversation_handle
GO

--store 0002 gets rejected status, end dialog message and ends conversation
DECLARE @message_body XML,
@message_type_name nvarchar(256),
@conversation_handle uniqueidentifier;
--note @conversation_handle value in WHERE clause is same as initial SEND
RECEIVE
@message_body = CAST(message_body AS XML),
@message_type_name = message_type_name
FROM dbo.LoanTakerQueue
WHERE conversation_handle = @conversation_handle;
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:c665b816-8f9e-41fd...@v15g2000vbb.googlegroups.com...

[Farmer]

GeLeet

 

 

Conversations can live indefinitely. You can start conversation and then just keep sending messages, never closing it.

When conversation is started and in  CONVERSING state, it has 3 key values.

 

ConversationID        (the same for both )

InitiatorConversationHandle    (is_initiator=1)

TargetConversationHandle    (is_initiator=1)

 

conversation_handle                                                conversation_id                                             is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc
B1EBFD20-39E2-DD11-B2DC-005056C00008 07965455-4895-43C2-BBF8-007F706E8360 0 65540 B0EBFD20-39E2-DD11-B2DC-005056C00008 65537 2077-02-01 15:58:56.550 CO CONVERSING
AEEBFD20-39E2-DD11-B2DC-005056C00008 07965455-4895-43C2-BBF8-007F706E8360 1 65540 AFEBFD20-39E2-DD11-B2DC-005056C00008 65536 2077-02-01 15:58:56.550 CO CONVERSING

 

So, one can create two services: LoanRequests and LoanProcessors.

 

For each LoanProcessor you start conversation from LoanRequests service to LoanProcessors service. send empty dummy message, get values for the above 3 values and receive dummy message.

 

Once conversation is established, you can persist these values in state table LoanProcessors.

 

Imagine, you established communication lines from LoanRequests ---> LoanProcessors. Each LoanProcesor has now a handle to receive on WHERE conversation_handle = @TargetConversationHandle

To send messages, you would use InitiatorConversationHandle.

 

Then, on your store side, you can

 

BEGIN CONVERSATION (new, one time use, closed at the end) from LoanProcessors to LoanRequests service, send a message and commit.

This will give you also @ConversationHandle_StoreSendsOn, a conversation handle for use by LoanProcesor to send back reply messages to each particular store request.

 

Also, at this point, this will allow you to get get target conversation handle, to use to wait to receive replies from LoanProcessors. 

 

    SELECT @ConversationHandle_StoreReceivesOn = target.Conversation_Handle
    FROM sys.conversation_endpoints init WITH (NOLOCK)
    JOIN sys.conversation_endpoints target WITH (NOLOCK) ON target.conversation_id = init.conversation_id
    WHERE init.is_initiator = 1
    AND target.is_initiator = 0
    AND init.Conversation_Handle = @ConversationHandle_StoreSendsOn

Now you can formulate a request to LoanProcesor, with whatever loan information and ALSO add to this request a @ConversationHandle_StoreSendsOn value, which will be ReturnConversationHandle.

You basically are telling LoanProcessor which store requested service and give LoanProcessor a conversation handle value to use to send reply back to a store.

 

Some code for you

    -- Include in the Request the resolved target Reply handle, one to be used to send Reply on
    -- Text replacement
    -- SET @Request = REPLACE(@Request, N'ReturnConversationHandle=""', N'ReturnConversationHandle="' + CAST(@ConversationHandle_ConsoleSendsOn as nchar(36)) + N'"');
    -- XML modify of an attribute
    SET @Request.modify('replace value of (/REQUEST/@ReturnConversationHandle)[1] with sql:variable("@ConversationHandle_ConsoleSendsOn")');

 

A store is in "listening" mode now, waiting to receive reply back from LoanProcesosr.

 

        WAITFOR
        (
            RECEIVE TOP(1)
                 @Reply = cast(cast(message_body as XML) as nvarchar(max))
                ,@ReplyType = message_type_name
            FROM [dbo].[StoreQueue]
            WHERE conversation_handle = @ConversationHandle_StoreReceivesOn
           
        ), TIMEOUT @Receive_TimeoutMS

 

This works very well for me in one database. I have not tested nor have any designs to try accross different databases and server. You can try. I think principles will hold.

In fact, this allows "multiplexing" of services and requestors. any requestor can pick a service to ask for something, give in the message how to get back to it, and service will get back to requestor.

 

I have an Enterprise Resource Planning (ERP) system working on this design, with state machine, sophisticated workflow engine implemented this way. It is so cool and all thanks to SQL Server Broker.

 

Some will come back to this post and say that conversations are ment to be one time use, like condoms. :) not really. it is very powerful to also consider them living permanently. Mr. Roger Wolter bentioned this once and I caught to the idea.

I hope this helps.

 

Thanks

 

Vladimir (Farmer)

 

 

P.S. Here is some schema sketch, some ideas (from my designs). You translate into your specific code. :)

 

 

CREATE TABLE [dbo].[LoanProcessors](
 [LoanProcHostName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LoanProcServiceName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [usrID] [int] NOT NULL,
 [ConversationID] [uniqueidentifier] NULL,
 [InitiatorConversationHandle] [uniqueidentifier] NULL,
 [TargetConversationHandle] [uniqueidentifier] NULL,
 CONSTRAINT [PK_LoanProcessors_LoanProcHostName_LoanProcServiceName] PRIMARY KEY CLUSTERED
(
 [LoanProcHostName] ASC,
 [LoanProcServiceName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

For each LoanProcessor

            BEGIN DIALOG CONVERSATION @ConversationHandle
            FROM SERVICE [SSB/Services/LoanRequestsQueueService]
            TO SERVICE N'SSB/Services/LoanProcQueueService', 'CURRENT DATABASE'
            ON CONTRACT [someContract]
            WITH ENCRYPTION = OFF;

 

            SEND ON CONVERSATION @ConversationHandle;

 

            UPDATE wh
            SET wh.ConversationID = conversation_id
                ,wh.InitiatorConversationHandle = ce.Conversation_Handle
                ,@TargetConversationHandle = wh.TargetConversationHandle = -- get this value too
                (
                    select ce2.Conversation_Handle
                    FROM sys.conversation_endpoints ce2 WITH (NOLOCK)
                    WHERE ce2.conversation_id  = ce.conversation_id
                    AND is_initiator = 0
                )
            FROM dbo.LoanProcessors wh, sys.conversation_endpoints ce WITH (NOLOCK)
            WHERE wh.LoanProcHostName = @LoanProcHostName
            AND wh.LoanProcServiceName = @LoanProcServiceName
            AND ce.Conversation_Handle = @ConversationHandle
            AND is_initiator = 1;

 

            -- Receive the above empty message, something about it
            RECEIVE @message_type_id = message_type_id
            FROM dbo.someQueue
            WHERE conversation_handle = @TargetConversationHandle

 

 

    -- Loop to receive initiating messages
    WHILE @Reply IS NULL
    BEGIN

 

        BEGIN TRANSACTION ;

 

        WAITFOR
        (
            RECEIVE TOP(1)
                 @Reply = cast(cast(message_body as XML) as nvarchar(max))
                ,@ReplyType = message_type_name
            FROM [dbo].[StoreQueue]
            WHERE conversation_handle = @ConversationHandle_StoreReceivesOn
           
        ), TIMEOUT @Receive_TimeoutMS
        ;
        -- this can happen only when an error message type is not received (somehow)
        IF @@ROWCOUNT = 0
        BEGIN
              IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
              BREAK
        END

 

        COMMIT TRANSACTION ;
   
        IF @ReplyType = N'DEFAULT'
        BEGIN
            SET @Reply = NULL;
            SET @ReplyType = NULL;
        END

 

        IF @Reply IS NOT NULL OR @ReplyType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' -- real question came through or timeout error
        BEGIN
            -- Close the Store conversation completely
            END CONVERSATION @ConversationHandle_StoreReceivesOn;
            END CONVERSATION @ConversationHandle_StoreSendsOn;
            BREAK;
        END
    END

 

    -- Output as a result set
    SELECT @Reply as Reply
        ,@ReplyType as ReplyType

 

 

 

 

 

 

"GiJeet" <gij...@yahoo.com> wrote in message news:11068af3-ed02-4ae3...@n21g2000vba.googlegroups.com...