Best method to call a long stored proc with WCF

Discussion in 'Microsoft ASP .NET' started by Cindy Lee, Jul 29, 2010.

  1. Cindy Lee

    Cindy Lee Guest

    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.
     
    Cindy Lee, Jul 29, 2010
    #1
    1. Advertisements

  2. Cindy Lee

    Cindy Lee Guest

    On Jul 29, 12:24 pm, Cindy Lee <> 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.
     
    Cindy Lee, Jul 29, 2010
    #2
    1. Advertisements

  3. Cindy Lee

    Cindy Lee Guest

    On Jul 29, 12:40 pm, Cindy Lee <> wrote:
    > On Jul 29, 12:24 pm, Cindy Lee <> 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.
     
    Cindy Lee, Jul 29, 2010
    #3
  4. Cindy Lee

    Cindy Lee Guest

    On Jul 29, 12:47 pm, Cindy Lee <> wrote:
    > On Jul 29, 12:40 pm, Cindy Lee <> wrote:
    >
    >
    >
    >
    >
    > > On Jul 29, 12:24 pm, Cindy Lee <> 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/
     
    Cindy Lee, Jul 29, 2010
    #4
  5. Cindy Lee

    Mr. Arnold Guest

    "Cindy Lee" <> wrote in message
    news:...
    On Jul 29, 12:47 pm, Cindy Lee <> wrote:
    > On Jul 29, 12:40 pm, Cindy Lee <> wrote:
    >
    >
    >
    >
    >
    > > On Jul 29, 12:24 pm, Cindy Lee <> 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.
     
    Mr. Arnold, Jul 30, 2010
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jason Collins

    sql server session state stored proc recompilation

    Jason Collins, Aug 26, 2003, in forum: Microsoft ASP .NET
    Replies:
    5
    Views:
    343
    Jason Collins
    Aug 28, 2003
  2. Mo
    Replies:
    3
    Views:
    188
  3. David Lozzi
    Replies:
    3
    Views:
    564
    David Lozzi
    Jun 1, 2005
  4. Simon

    AJAX enabled WCF Service Vs Standard WCF Service

    Simon, Oct 13, 2009, in forum: Microsoft ASP .NET
    Replies:
    0
    Views:
    401
    Simon
    Oct 13, 2009
  5. Cindy Lee
    Replies:
    1
    Views:
    637
    Mr. Arnold
    Mar 19, 2010
Loading...

Share This Page