Roger,

thanks for your help so much.

It's wonderful that you help others on newsgroups like this. I have great respect for people like you.
Good work!

This all actually works now (requires polishing yet, of course).

I would like to mention that no cursor is used in my implementation. MS should remove it from SQL Server commands. :)

In addition, I wonder if I did make clearer for you my dillemas, with this implementation?

If I try to use message type or queues for this, implemetation would get complicated in a hurry as this is metadata. I seek ways to not link metadata to data.
With my knowledge of SSB, I see no other way thus far.

By the way, I will add code to autostart conversations in sorting queue if they are not started already and add trigger to stop conversations if action type is deleted. Then this will automaintain itself.

Thank you again.

Farmer (V*l*a*d*i*m*i*r M*o*l*d*o*v*a*n*e*n*k*o)

 Roger,
 thank you so much for your invaluable support.
 I appreciate you trying to understand.
 In fact, I do take full advantage of SSB and try to minimize any setups and keep it simple. I am also quickly ramping my knowledge on this (exponentially) :)

 Message Type is the metadata construct and that is the problem in my situation, with what I want to do.
 let me explain a bit clearer.

 Let's say we have these actions of these action types.

 Action  ActionType
 -------------------------------
 SProc1  StoredProcedure
 Sproc2  StoredProcedure
 SProc3  StoredProcedure
 CR1  CrystalReport
 CR2  CrystalReport
 Email1  eMail
 Email2  eMail

 Users can add new actions and new action types at will. There may be 2000 actions of 50 types for all that matters.

 If I associate Message Type to either of them, it will be bad because I would drown in message these messages. Each time new type or action is added, new message types need to be defined, contracts and what not needs to be adjusted.

 On the receiving end, I have workers capable of running actions of these types.

 -----------------------------------------------------------------
 |                  Submission Queue                             |
 -----------------------------------------------------------------
                        |(this conv dialog will be auto ended all the time)
                        |
 -----------------------------------------------------------------
 |                  Sorting Queue                                |
 -----------------------------------------------------------------
  |                      |                      |
  |(SP conversation)     |(CR conversation)     |(EM conversation)
  |                      |                      |
 -----------------------------------------------------------------
 |              Receiving Queue                                  |
 -----------------------------------------------------------------
      |waitfor              |waitfor              |waitfor
 -----------------     ----------------     ----------------
 |StoredProcedure|     |CrystalReports|     |eMail          |
 ----------------      ----------------     ----------------

 Process:

 When change happens, on anything, let's say the fact of your salary raise, let us say up your salary by 20%, on this change needs to run SProc1, SProc2 and CR1 actions. The task to process would be like below. The message type in this case would be called "Task". In fact, it would be only on message type anywhere here. Only one.

 <Tasks>
   <row who="Roger" action="SProc1" actiontype="StoredProcedure"/>
   <row who="Roger" action="SProc2" actiontype="StoredProcedure"/>
   <row who="Roger" action="CR1"    actiontype="StoredProcedure"/>
 </Tasks>

 I want to submit this as a unit, qickly, ot of trigger with no post-processing send to sorting queue, which will split the original task batch into atomic tasks and send them on permatent conversations respecting their types towards Receiving queue.

 <Tasks>
   <row who="Roger" action="SProc1" actiontype="StoredProcedure"/>
 </Tasks>

 <Tasks>
   <row who="Roger" action="SProc2" actiontype="StoredProcedure"/>
 </Tasks>

 <Tasks>
   <row who="Roger" action="CR1"    actiontype="StoredProcedure"/>
 </Tasks>

 I want each worker receive only one and only one action at a time.
 I will

         WAITFOR (
             RECEIVE TOP(1)
                 @conversation_group_id = conversation_group_id
                 ,@message_type_name = message_type_name
                 ,@Message =  CAST(message_body AS XML)
             FROM [dbo].[inResponseWorkerQueue]
             WHERE conversation_handle = @conversation_handle (a vale of receiving conv handle of StoredProcedure type )
         );

 In this scenario, if users add new actions, new types, all I need to do is to make sure I open new conversation dialogs and assosiate conversation_id with new type. I don't need new queues, new message types, contracts. I would not need to re-create Service Broker definitions and adding new conversation can be done in ONLINE mode

 That is the plan and it is (almost) working.

 Now, this receive portion seems to be faltering for me. I posted separate message about it.
 I get cross-receive happening where I receive StoredProcedure in CrystalReports WAITFOR

 I don't understand why? I am positive on conversation handle values that I use the target receiving values for the same corresponding conversation_id.

 Thanks

 "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message news:uLYzrY68GHA.4552@TK2MSFTNGP05.phx.gbl...
 > You shouldn't have to send an empty message or set up a return conversation.
 > Dialog Conversations are bidirectional so when you set one up you
 > automatically define the return path.  That's why the FROM service is
 > required.  Whatever queue you associate with the FROM service will get any
 > messages sent form the target of the dialog.
 >
 > I don't see why you are closing the conversation after the initial message.
 > Open the conversation once when you install the software and keep it open
 > forever.  You can map the conversation handle to the action when you create
 > it.
 >
 > CONVERSATION's are closed only when they're gone so you can't rely on
 > getting any information from a closed conversation.  Conversation Groups are
 > not the same on both endpoints either so I'm not sure what the conversation
 > group id is adding to your solution.
 >
 > In general, you seem to be doing a lot of work to replicate the
 > functionality that service broker provides by letting you define a message
 > type.  If you were willing to use message types for each action, you could
 > have a single service receiving messages and dispatching work to all your
 > actions while letting service broker do the tracking for you.
 >
 > --
 > This posting is provided "AS IS" with no warranties, and confers no rights.
 > Use of included script samples are subject to the terms specified at
 > http://www.microsoft.com/info/cpyright.htm
 >
 > "Farmer" <someone@somewhere.com> wrote in message
 > news:%23p3Kb6t8GHA.208@TK2MSFTNGP03.phx.gbl...
 >> Thank you Roger,
 >> Your comment
 >> "Note that the conversation_handle's are different for the two endpoint of
 >> a conversation."
 >> got me real worried at first as this put a small wrench into my plan.
 >> But then I quickly recovered. Your second statement about conversation_id
 >> can be taken advantage of, and I can BEGIN DIALOG CONVERSATION for each
 >> action type, then lookup conversation_ids in sys.conversation_endpoints,
 >> save them on action type records. Then send empty message on each
 >> conversation_handle, that would start return conversations, with the new
 >> different receiving end conversation_handles, but the same conversation
 >> id. When a worker thread starts to process action type it is capable of
 >> processing, I would use saved conversation_id to lookup in
 >> sys.conversation_endpoints the receiving end conversation_handle and then
 >> start RECEVEing using it in WHERE clause. I would never end these two
 >> conversations and just keep sending and receiving forever. These
 >> conversations will always be  CONVERSING.
 >>
 >> How is my idea of "action tree" processing using
 >> RELATED_CONVERSATION_GROUP? Is it sound at all?
 >> Just to recap:
 >> if I have such action tree
 >>
 >> "ExportToPDF"
 >>    "EmailPDF"
 >>        "RunReport"
 >>
 >> and if I BEGIN DIALOG CONVERSATION  and send  just "ExportToPDF" action
 >> request for processing, capture its RELATED_CONVERSATION_GROUP (GUID1) and
 >> send it together with "ExportToPDF" action request. I close this
 >> conversation.
 >> I process this action and when I am done with "ExportToPDF" action
 >> I use the same RELATED_CONVERSATION_GROUP  to begine new conversation
 >> BEGIN DIALOG CONVERSATION WITH RELATED_CONVERSATION_GROUP  = GUID1 to send
 >> "EmailPDF" action on this new conversation handle. I close it, process
 >> this action. I use the same RELATED_CONVERSATION_GROUP  = GUID1 and BEGIN
 >> new DIALOG CONVERSATION  and send "RunReport" to process. I close this
 >> conversation.
 >>
 >> I hope that this will ensure that my receiving end will receive these in
 >> sequece of the tree, "ExportToPDF" > "EmailPDF" >  "RunReport", regardless
 >> of what type these actions are and that their sequece will not be somehow
 >> mixed, i.e. "RunReport" must not be executed before others somehow.
 >>
 >> Question: Is conversation group available to BEGIN DIALOG CONVERSATION
 >> WITH RELATED_CONVERSATION_GROUP  = GUID1  if all conversations under this
 >> group are closed?
 >> This is important question for me as the above logic hinges on it.
 >>
 >> I suppose I don't have to close conversations and close them at the very
 >> end, after "RunReport" action, all that are related.
 >>
 >> thanks
 >> Farmer
 >>
 >>
 >> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
 >> news:%235cR4Km8GHA.1252@TK2MSFTNGP04.phx.gbl...
 >>> I'm not sure what you mean by prime.  If you create a dialog for each
 >>> action type and keep track of the dialog handle.  When you want to
 >>> trigger an action you look up the dialog handle for the action type and
 >>> do a send. On the receive side, you can recognize the action type by the
 >>> dialog handle. Note that the conversation_handle's are different for the
 >>> two endpoint of a conversation.  The conversation_id is the same for both
 >>> endpoints so you can use this in the sys.conversation_endpoints to figure
 >>> out which handle maps to each action.
 >>>
 >>> --
 >>> This posting is provided "AS IS" with no warranties, and confers no
 >>> rights.
 >>> Use of included script samples are subject to the terms specified at
 >>> http://www.microsoft.com/info/cpyright.htm
 >>>
 >>> "Farmer" <someone@somewhere.com> wrote in message
 >>> news:u5KRtVi8GHA.2128@TK2MSFTNGP05.phx.gbl...
 >>>> Also, I can see how I can prime my system:
 >>>>
 >>>> just have some code to BEGIN CONVERSATIONs for each action type, SENDing
 >>>> nothing. If new action types get added by user, BEGIN DIALOG
 >>>> CONVERSATION on the adding of that action type.
 >>>>
 >>>> I am slowly getting it... :)
 >>>>
 >>>> "Farmer" <someone@somewhere.com> wrote in message
 >>>> news:eDceXkh8GHA.1248@TK2MSFTNGP03.phx.gbl...
 >>>>> Hi Roger,
 >>>>>
 >>>>>
 >>>>>
 >>>>> I appreciate your help very much.
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>> I have been reading your blog very carefully and I am getting good
 >>>>> ideas from it. Thank you so much for posting your blog.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Here is how I can see my design further using posted by you info.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Let us say I have an Order table and it has several rows of data. I
 >>>>> have an update trigger on this table. If the status field is updated
 >>>>> with the change, the trigger fires and requests several
 >>>>>
 >>>>>
 >>>>> actions to fire, depending on that status change is. These actions can
 >>>>> be action trees in fact, and these trees and their branches can be
 >>>>> conditionally executed depending on the outcomes of their
 >>>>>
 >>>>> parent. These action as I was stating before can be of different types.
 >>>>> These actions are also persisted in my own database queues (that is how
 >>>>> it works), with more information about actual tasks.
 >>>>>
 >>>>>
 >>>>>
 >>>>> My idea now, correlated to the information I soaked from your blog, is
 >>>>> to have three queues.
 >>>>>
 >>>>> 1. submission    initiator
 >>>>>
 >>>>> 2. postprocessing (target for submission and initiator for worker)
 >>>>>
 >>>>> 3. worker (target for postprocessing)
 >>>>>
 >>>>>
 >>>>>
 >>>>> ordID    Status
 >>>>>
 >>>>> 100        20
 >>>>>
 >>>>> 101        20
 >>>>>
 >>>>> 102        30
 >>>>>
 >>>>> 110        30
 >>>>>
 >>>>>
 >>>>>
 >>>>> So let's say I
 >>>>>
 >>>>>
 >>>>>
 >>>>> UPDATE dbo.orders
 >>>>>
 >>>>> SET Status = 30
 >>>>>
 >>>>> WHERE ordID IN (100,101)
 >>>>>
 >>>>>
 >>>>>
 >>>>> This update requests actions 1000 (output pdf file), and 1001 (run
 >>>>> report) to fire. Action 1000 has a dependent action 1010 (email action
 >>>>> to email generated pdf).  Therefore, 1000 and 1010 must be processed
 >>>>> serially. But the initial request contains only two actions to run:
 >>>>> 1000 and 1001.
 >>>>>
 >>>>>
 >>>>>
 >>>>> On this update, I would know that this is an initial action request (by
 >>>>> absence of related conversation_group ID, see below).
 >>>>>
 >>>>> I will create an XML task request
 >>>>>
 >>>>> (
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1000",action="1000"
 >>>>>
 >>>>>    <ordID="1000",action="1001"
 >>>>>
 >>>>>    <ordID="1001",action="1000"
 >>>>>
 >>>>>    <ordID="1001",action="1001"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>> , and submit it into "submission" qeueue.
 >>>>>
 >>>>>
 >>>>>
 >>>>> This task batch gets delivered to "postprocessing" queue.
 >>>>> Postprocessing activation SQL stored procedure receives this XML task
 >>>>> request, acknowledge task batch receipt to "submission" queue", busts
 >>>>> it up into atomic tasks and submits these tasks into postprocessing
 >>>>> queue, towards worker queue, following this logic:
 >>>>>
 >>>>>
 >>>>>
 >>>>> I send these atomic tasks towards worker queue.
 >>>>>
 >>>>>
 >>>>>
 >>>>> derive atomic task and send:
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1000",action="1000", conversation_group ="GUID101"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1000",action="1001", conversation_group ="GUID102"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1001",action="1000", conversation_group ="GUID103"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1001",action="1001", conversation_group ="GUID104"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>> Remember, each action is of a specific type. On the first atomic task
 >>>>> for the action 1000 (output pdf file)  I get a new conversation handle.
 >>>>> I will persist it in the database as well, storing it on this action
 >>>>> type record, Outputpdf Type = GUID1.
 >>>>>
 >>>>> For the second task, action type is RunReport and I presist it as GUID2
 >>>>>
 >>>>>
 >>>>>
 >>>>> On the worker queue, I will have an array of workers that are latched
 >>>>> onto this worker queue.
 >>>>>
 >>>>> Each worker thread is capable of processing one action type and one
 >>>>> action at the time.
 >>>>>
 >>>>> They will start, read database and get GUID types for actions they can
 >>>>> handle and start waiting for
 >>>>>
 >>>>>
 >>>>>
 >>>>> thread1    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID1
 >>>>> (for Outputpdf type)
 >>>>>
 >>>>> thread2    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID1
 >>>>> (for Outputpdf type)
 >>>>>
 >>>>> thread3    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID1
 >>>>> (for Outputpdf type)
 >>>>>
 >>>>>
 >>>>>
 >>>>> thread4    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID2
 >>>>> (RunReport)
 >>>>>
 >>>>> thread5    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID2
 >>>>> (RunReport)
 >>>>>
 >>>>>
 >>>>>
 >>>>> thread6    WAITFOR( RECEIVE TOP (1).. WHERE conversation_handle = GUID3
 >>>>> (email)
 >>>>>
 >>>>>
 >>>>>
 >>>>> When the task arrives, it gets processed by a worker thread (1,2,or 3,
 >>>>> any that is capable of GUID1, OutputPDF).
 >>>>>
 >>>>> Let us say it's task
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1000",action="1000"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>> When worker is done, it updates my own database queue via update stored
 >>>>> procedure, with the fact that it is done, the parent return code. I
 >>>>> would also pass conversation_group ID to this update procedure for this
 >>>>> task.
 >>>>>
 >>>>> The Internal queue Update procedure will use this conversation_group ID
 >>>>> as the RELATED_CONVERSATION_GROUP = related_conversation_group_id to
 >>>>> release another dependent task, which will create a new dialog with the
 >>>>> new conversation_handle for email action type and persist it in the
 >>>>> database as GUID3.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Action type RunReport will end its execution, as there are no dependent
 >>>>> actions to process further.
 >>>>>
 >>>>>
 >>>>>
 >>>>> The new email action task batch, dependent on OutptPDF action, would be
 >>>>> like this:
 >>>>>
 >>>>> <Tasks>
 >>>>>
 >>>>>    <ordID="1000",action="1010"
 >>>>>
 >>>>>    <ordID="1001",action="1010"
 >>>>>
 >>>>> /><Tasks>"
 >>>>>
 >>>>>
 >>>>>
 >>>>> postprocessing queue will pass get these new conversation group IDs and
 >>>>> pass them to the Internal queue Update procedure but there would be no
 >>>>> further actions to run.
 >>>>>
 >>>>>
 >>>>>
 >>>>> When order rows get updated again, and new action batch is received at
 >>>>> postprocessing queue, posprocesing procedure would re-use Action Type
 >>>>> GUIDs as RELATED_CONVERSATION = GUID? (persisted by action type)
 >>>>>
 >>>>>
 >>>>>
 >>>>> There is a question yet how to start workers, with right conversation
 >>>>> handles, when the system is in the initial state, when no actions are
 >>>>> in the system. But that is minor.
 >>>>>
 >>>>> I want to make sure that Service Broker logic with conversation_groups
 >>>>> and conversation_handles is sound.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Am I on the right track here?
 >>>>>
 >>>>>
 >>>>>
 >>>>> I would be ecstatic if such guru like you can confirm or shoot holes in
 >>>>> this logic.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Thank you so much.
 >>>>>
 >>>>>
 >>>>>
 >>>>> Farmer (V*l*a*d*i*m*i*r M*o*l*d*o*v*a*n*e*n*k*o)
 >>>>>
 >>>>> Database Developer, DBA
 >>>>>
 >>>>> http://www.2020technologies.com
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
 >>>>> news:uveoRv87GHA.3396@TK2MSFTNGP04.phx.gbl...
 >>>>>> You don't have to start a conversation each time.  Just start it once
 >>>>>> when you create the queue and leave it open until you decide you don't
 >>>>>> want to use that action any more.  Conversation can last years.  By
 >>>>>> default there's one conversation in a conversation group so it doesn't
 >>>>>> matter which you use. You can pick a conversation group id however so
 >>>>>> there may be some advantage there.  You can also have more than one
 >>>>>> conversation in a conversation group also so this would allow you to
 >>>>>> use some parallelism if necessary.
 >>>>>>
 >>>>>> The only advantage to using Get conversation group seems to be you can
 >>>>>> look at the conversation group and start up an appropriate receive to
 >>>>>> handle the message.  This allows you to have only a single thread
 >>>>>> waiting for messages but from your earlier post it sounds like you
 >>>>>> want a process for each action type waiting for messages of that type.
 >>>>>> If that's the case, there's no point in using Get Conversation Group -
 >>>>>> you can just do the receive from each process.
 >>>>>>
 >>>>>> --
 >>>>>> This posting is provided "AS IS" with no warranties, and confers no
 >>>>>> rights.
 >>>>>> Use of included script samples are subject to the terms specified at
 >>>>>> http://www.microsoft.com/info/cpyright.htm
 >>>>>>
 >>>>>> "Farmer" <someone@somewhere.com> wrote in message
 >>>>>> news:%23w8RYV77GHA.4552@TK2MSFTNGP05.phx.gbl...
 >>>>>>> Thank you very much Roger, for your ideas.
 >>>>>>>
 >>>>>>> A queue per action type is on the surface simpler. Action types are
 >>>>>>> quite dynamic. I said 12 now but users can add them as they wish.
 >>>>>>> Each time I would need to be creating two queues for action type.
 >>>>>>> This sounds like queue maintenence nightmare.
 >>>>>>> I would like to try to stick to one sending and one receiving queue
 >>>>>>>
 >>>>>>> I like your other idea of conversation dialog  or maybe even
 >>>>>>> conversation group.
 >>>>>>>
 >>>>>>> One action type = one conversation dialog
 >>>>>>> Or is it
 >>>>>>> One action type = one conversation dialog group
 >>>>>>>
 >>>>>>> I am not sure what is better or right here
 >>>>>>>
 >>>>>>> If I assign a fixed GUID to Action Type and store it in the database.
 >>>>>>> Each time I want to send actions, I start conversations and use
 >>>>>>> RELATED_CONVERSATION_GROUP or RELATED_CONVERSATION for each action
 >>>>>>> type, using a value permanently assigned to Action Type.
 >>>>>>>
 >>>>>>> On the receiving end, all my workers would be waiting with
 >>>>>>>
 >>>>>>> BEGIN TRAN
 >>>>>>> WAITFOR (GET CONVERSATION GROUP
 >>>>>>>
 >>>>>>> As soon as new group has arrived, evaluate that one that was gotten
 >>>>>>> by the above is one the worker can process. If yes, receive messages
 >>>>>>> for this group, otherwise ROLLBACK imideately and then other worker
 >>>>>>> will get a try to receive this group.
 >>>>>>> Logical draback here seems like there would a  whole bunch of
 >>>>>>> conversation groups trying to find their appropriate workers.
 >>>>>>> Sadly, there is no WHERE clause for the GET CONVERSATION GROUP
 >>>>>>>
 >>>>>>> Now with "action type = conversation dialog".
 >>>>>>> I let conversation groups auto increment, but always use
 >>>>>>> RELATED_CONVERSATION = action type GUID to send messages and receive
 >>>>>>> them
 >>>>>>>
 >>>>>>> Each worker will use WHERE conversation_handle = GUID of action type
 >>>>>>> he can deal with.
 >>>>>>> This sounds better but logically any worker will be locking groups it
 >>>>>>> may not be receiving any messages from, similar to group issue.
 >>>>>>>
 >>>>>>> Or am I missing something here?
 >>>>>>>
 >>>>>>> Farmer
 >>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
 >>>>>>> news:%23LyVSNx7GHA.4116@TK2MSFTNGP03.phx.gbl...
 >>>>>>>>A queue per action is probably the simplest architecture.  You could
 >>>>>>>>also have a conversation (dialog) for each event type and use the
 >>>>>>>>where option to receive from a particular dialog.  This actually is
 >>>>>>>>slightly more efficient. A dialog is persistent and can last forever.
 >>>>>>>>
 >>>>>>>> In the queue per action option you can make the FROM service the
 >>>>>>>> same for all actions so with 12 actions you would only need 13
 >>>>>>>> queues if that helps.
 >>>>>>>>
 >>>>>>>> I have several applicable articles in my blog:
 >>>>>>>>
 >>>>>>>> http://blogs.msdn.com/rogerwolterblog/default.aspx
 >>>>>>>>
 >>>>>>>>
 >>>>>>>> --
 >>>>>>>> This posting is provided "AS IS" with no warranties, and confers no
 >>>>>>>> rights.
 >>>>>>>> Use of included script samples are subject to the terms specified at
 >>>>>>>> http://www.microsoft.com/info/cpyright.htm
 >>>>>>>>
 >>>>>>>> "Farmer" <someone@somewhere.com> wrote in message
 >>>>>>>> news:uX6BL3w7GHA.2268@TK2MSFTNGP05.phx.gbl...
 >>>>>>>>> Hello.
 >>>>>>>>> I am new to service broker and my experience is short with it,
 >>>>>>>>> nevertheleless I can see great potential in it. I experimented with
 >>>>>>>>> simple queues and messages and I am impressed how it all works.
 >>>>>>>>>
 >>>>>>>>> I have the following architectural design that I am trying to
 >>>>>>>>> implement using Service Broker.
 >>>>>>>>>
 >>>>>>>>> What I am trying to accomplish is this: I have triggers on several
 >>>>>>>>> tables that when status changes on data, certain actions are
 >>>>>>>>> requred to run on these changes. These actions are of several
 >>>>>>>>> types: for example, print crystal reports or launch an email or run
 >>>>>>>>> stored procedure. These action are executed by workers on different
 >>>>>>>>> computers and these workers know how to execute actions of one or
 >>>>>>>>> several types. these workers are all connected to the same SQL
 >>>>>>>>> Server and know how to communicate with database.  I have a dozen
 >>>>>>>>> of action types.
 >>>>>>>>>
 >>>>>>>>> We have an existing infrustructre (non-service broker) that
 >>>>>>>>> determines which worker gets what and when.
 >>>>>>>>>
 >>>>>>>>> Here is an example of typical communication.:
 >>>>>>>>> Trigger fires, compiles xml  message that contains requests for
 >>>>>>>>> actions 1,2,3,4. Actions 1,3 are of stored procedure type, 2 is
 >>>>>>>>> Crystal Reports, 4 is an email request. Coordinator process
 >>>>>>>>> received these action requests and distributes these to workers by
 >>>>>>>>> type, which in turn execute these. Workers of type 1,2,3 can be on
 >>>>>>>>> PC1 and 4 is on PC2
 >>>>>>>>>
 >>>>>>>>> So how can I model this in Service Broker?
 >>>>>>>>>
 >>>>>>>>> I would like to have these workers "latch" onto a queue with
 >>>>>>>>> WIATFOR(RECEIVE and wait till the next message. I thought that I
 >>>>>>>>> can use Message Type to denote action type to receive for each
 >>>>>>>>> worker. But in the RECEIVE WHERE clause I can't specify
 >>>>>>>>> message_type_name ='ActionType1'. It seems to list converation
 >>>>>>>>> handles and groups as the only valid columns for where clause. If a
 >>>>>>>>> worker receives the top message, then it may not be of type it can
 >>>>>>>>> process. I don't want to also worry abot interporcess
 >>>>>>>>> communications to pass it to peer workers. In addition, to
 >>>>>>>>> complicate, I can have several workers latched onto a queue that
 >>>>>>>>> are capable to process the same action type (let us say 3 worker
 >>>>>>>>> threads of the same type)
 >>>>>>>>>
 >>>>>>>>> Then I thought that if I create initiator > target queue pairs for
 >>>>>>>>> each action type, and in the triggers I send messages into these
 >>>>>>>>> queues by action type. This seems like workable idea but it requres
 >>>>>>>>> me to add new queues whenever new action types get added and it
 >>>>>>>>> seems too many queues to manage as I have a dozen of action types.
 >>>>>>>>> Therefore, that would be 12 send and 12 receive queues already.
 >>>>>>>>>
 >>>>>>>>> Is there a better way applicable here that any of you can suggest?
 >>>>>>>>> What is better design architechure for this infrastructure within
 >>>>>>>>> Service Broker capabilities?
 >>>>>>>>>
 >>>>>>>>> Thank you so much for taking time to understand my delemma and your
 >>>>>>>>> answer.
 >>>>>>>>>
 >>>>>>>>> Farmer
 >>>>>>>>>
 >>>>>>>>
 >>>>>>>>
 >>>>>>>
 >>>>>>>
 >>>>>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>
 >>>>
 >>>
 >>>
 >>
 >>
 >
 >