November 12, 2007 at 4:11 am
Not sure if this is the right forum for this question but here goes. I want to be able to queue a set of jobs and execute them asynchronously. The list of jobs will be dynamic in that there is not a predetermine number of jobs. Does anyone know of a way of doing this?
Thanks
November 12, 2007 at 4:20 am
One way of doing this is to use the Service Broker in SQL Server 2005. Set up a queue in it, and a stored procedure on this queue. (see create queue http://msdn2.microsoft.com/en-us/library/ms190495.aspx). You can specify the max number of executing stored procedures on this queue too, and writing to the queue is transactional. However, this solution does require some time learning how the Service Broker is working.
What kind of jobs are you trying to schedule? are they maintenance jobs or are they just data related operations that can be deferred and run asynchronously?
Regards,
Andras
November 12, 2007 at 4:25 am
Hi Andras,
The jobs are all other data related; I am distinguishing them from all other jobs by giving them a specific job category type. I did have a very, very brief look at SSSB but I got confused and so left it alone!!! Do you think that SSSB is the right tool to achieve this?
Thanks
November 12, 2007 at 8:24 am
David (11/12/2007)
Hi Andras,The jobs are all other data related; I am distinguishing them from all other jobs by giving them a specific job category type. I did have a very, very brief look at SSSB but I got confused and so left it alone!!! Do you think that SSSB is the right tool to achieve this?
Thanks
Hi David,
if you describe your problem in a bit more detail I'm sure we would be able to advise on a solution. Whether SSSB is the right one is a question that I cannot answer based on the current info :).
Andras
November 12, 2007 at 8:35 am
Hi Andras,
I have an ASP.NET form that when submitted will execute a stored procedure which in turn creates a job. The job deletes and populates data from the database but what I want to avoid is having multiple jobs all executing at the same time. So I initially thought about ways to check to see if a job of a particular type is running and if so, queue all other jobs of the same time and execute them one by one.
Hope this makes sense but please let me know if you need any more info.
Thanks
David
November 12, 2007 at 8:42 am
The two most common solutions for these problems I've seen are either using Service Broker (with a queue where the MAX_QUEUE_READERS is set to 1) or to use a polling application that polls the table for new "jobs".
The later has the advantage that it does not require you to learn into SSSB, but it is using more resources. (a very good and short book on the Service Broker is by Roger Wolter http://www.sqlservicebroker.com/
Others may have different solutions too 🙂
Regards,
Andras
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply