Best method to call a long stored proc with WCF
in [ASP .NET]
|
From: Cindy Lee on 29 Jul 2010 15:24 Say have a stored procedure that takes about 10min to 1 hour to run and I need to call it with WCF (silverlight). I'd basically want to fire off the query, have the wcf service fire off the query in a thread or something. Then have the wcf return to the silverlight program and say 'Ok the SP is running' The silverlight app would then make some wcf calls that would count the rows in table x, to see what the progress is. Or the SP could update another table with the status. Could the WCF service just call a thread return 'OK running', and still have the SP running on the database? I don't want to keep the connection open for 10 min to 1 hour. I'm a little shy about making a synchronis WCF service that will post back to the silverlight wcf client. Would that be easy to do, it sounds a lot more complicated.
From: Cindy Lee on 29 Jul 2010 15:40 On Jul 29, 12:24 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > Say have a stored procedure that takes about 10min to 1 hour to run > and I need to call it with WCF (silverlight). > > I'd basically want to fire off the query, have the wcf service fire > off the query in a thread or something. Then have the wcf return to > the silverlight program and say 'Ok the SP is running' > > The silverlight app would then make some wcf calls that would count > the rows in table x, to see what the progress is. Or the SP could > update another table with the status. Could the WCF service just call > a thread return 'OK running', and still have the SP running on the > database? I don't want to keep the connection open for 10 min to 1 > hour. > > I'm a little shy about making a synchronis WCF service that will post > back to the silverlight wcf client. Would that be easy to do, it > sounds a lot more complicated. Maybe I need to use a sql server agent and use sp_start_job to fire it off.
From: Cindy Lee on 29 Jul 2010 15:47 On Jul 29, 12:40 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > On Jul 29, 12:24 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > > > > Say have a stored procedure that takes about 10min to 1 hour to run > > and I need to call it with WCF (silverlight). > > > I'd basically want to fire off the query, have the wcf service fire > > off the query in a thread or something. Then have the wcf return to > > the silverlight program and say 'Ok the SP is running' > > > The silverlight app would then make some wcf calls that would count > > the rows in table x, to see what the progress is. Or the SP could > > update another table with the status. Could the WCF service just call > > a thread return 'OK running', and still have the SP running on the > > database? I don't want to keep the connection open for 10 min to 1 > > hour. > > > I'm a little shy about making a synchronis WCF service that will post > > back to the silverlight wcf client. Would that be easy to do, it > > sounds a lot more complicated. > > Maybe I need to use a sql server agent and use sp_start_job to fire > it off.- Hide quoted text - > > - Show quoted text - Thing is I'd rather not use sql server agent, and I think if I break connection to the WCF Service it will stop the stored procedure from running.
From: Cindy Lee on 29 Jul 2010 17:37 On Jul 29, 12:47 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > On Jul 29, 12:40 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > > > > On Jul 29, 12:24 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > Say have a stored procedure that takes about 10min to 1 hour to run > > > and I need to call it with WCF (silverlight). > > > > I'd basically want to fire off the query, have the wcf service fire > > > off the query in a thread or something. Then have the wcf return to > > > the silverlight program and say 'Ok the SP is running' > > > > The silverlight app would then make some wcf calls that would count > > > the rows in table x, to see what the progress is. Or the SP could > > > update another table with the status. Could the WCF service just call > > > a thread return 'OK running', and still have the SP running on the > > > database? I don't want to keep the connection open for 10 min to 1 > > > hour. > > > > I'm a little shy about making a synchronis WCF service that will post > > > back to the silverlight wcf client. Would that be easy to do, it > > > sounds a lot more complicated. > > > Maybe I need to use a sql server agent and use sp_start_job to fire > > it off.- Hide quoted text - > > > - Show quoted text - > > Thing is I'd rather not use sql server agent, and I think if I break > connection to the WCF Service it will stop the stored procedure from > running.- Hide quoted text - > > - Show quoted text - Again to answer my own question. I think a service broker would be the best This link provides a great solution: http://rusanu.com/2009/08/05/asynchronous-procedure-execution/
From: "Mr. Arnold" MR. on 30 Jul 2010 10:19 "Cindy Lee" <danparker276(a)yahoo.com> wrote in message news:14c75029-277d-4c06-b3f9-e76f5a6bce3f(a)y32g2000prc.googlegroups.com... On Jul 29, 12:47 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > On Jul 29, 12:40 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > > > > On Jul 29, 12:24 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > Say have a stored procedure that takes about 10min to 1 hour to run > > > and I need to call it with WCF (silverlight). > > > > I'd basically want to fire off the query, have the wcf service fire > > > off the query in a thread or something. Then have the wcf return to > > > the silverlight program and say 'Ok the SP is running' > > > > The silverlight app would then make some wcf calls that would count > > > the rows in table x, to see what the progress is. Or the SP could > > > update another table with the status. Could the WCF service just call > > > a thread return 'OK running', and still have the SP running on the > > > database? I don't want to keep the connection open for 10 min to 1 > > > hour. > > > > I'm a little shy about making a synchronis WCF service that will post > > > back to the silverlight wcf client. Would that be easy to do, it > > > sounds a lot more complicated. > > > Maybe I need to use a sql server agent and use sp_start_job to fire > > it off.- Hide quoted text - > > > - Show quoted text - > > Thing is I'd rather not use sql server agent, and I think if I break > connection to the WCF Service it will stop the stored procedure from > running.- Hide quoted text - > > - Show quoted text - Again to answer my own question. I think a service broker would be the best This link provides a great solution: http://rusanu.com/2009/08/05/asynchronous-procedure-execution/ ---------------------------------------------------------------------------------------------------------- You should get yourself a good book on MS SQL Server 2005/2008 Service Broker book. The article is a good start, and it is the correct path to take. You can call an async WCF Web service that calls calls SB async.
|
Pages: 1 Prev: System.math missing Next: Lock xml file writes through multiple aspx pages |