Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Service Broker (2005)  Service Broker Design

Author  Topic  Liam Starting Member 6 Posts Posted - 2010-08-04 : 06:41:04 Hi I’m looking to introduce SS Service Broker, I have a remote orders database and a local processing database, all activity on the processing database has to happen in sequence, this seems a perfect job for Service Broker! I’ve set up the infrastructure, I can send and receive messages and now I’m looking at the design of the processing. As I said all processes for one order need to be completed in sequence so I’ll put them in one conversation. One of these processes is a request for external flat file data, we then wait (could be several days) and then import and process this file when it returns. How can I process half the tasks, then wait for the flat file to return before processing the other half. I’ve had some ideas but I’m sure I’m missing a trick somewhere 1) Write all queue items to a status table and use status values – seems to remove some of the flexibility of SSSB and add another layer of tasks 2) Keep the transaction open until we get the data back – not ideal 3) Have the flat file import task continually polling for the file to appear – this seems inefficient What is the most efficient way of managing this workflow? thanks in advance