PC Review


Reply
Thread Tools Rate Thread

Best method to call a long stored proc with WCF

 
 
Cindy Lee
Guest
Posts: n/a
 
      29th Jul 2010
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.
 
Reply With Quote
 
 
 
 
Cindy Lee
Guest
Posts: n/a
 
      29th Jul 2010
On Jul 29, 12:24*pm, Cindy Lee <(E-Mail Removed)> 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.
 
Reply With Quote
 
 
 
 
Cindy Lee
Guest
Posts: n/a
 
      29th Jul 2010
On Jul 29, 12:40*pm, Cindy Lee <(E-Mail Removed)> wrote:
> On Jul 29, 12:24*pm, Cindy Lee <(E-Mail Removed)> 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.
 
Reply With Quote
 
Cindy Lee
Guest
Posts: n/a
 
      29th Jul 2010
On Jul 29, 12:47*pm, Cindy Lee <(E-Mail Removed)> wrote:
> On Jul 29, 12:40*pm, Cindy Lee <(E-Mail Removed)> wrote:
>
>
>
>
>
> > On Jul 29, 12:24*pm, Cindy Lee <(E-Mail Removed)> 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/asynchr...ure-execution/
 
Reply With Quote
 
Mr. Arnold
Guest
Posts: n/a
 
      30th Jul 2010

"Cindy Lee" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Jul 29, 12:47 pm, Cindy Lee <(E-Mail Removed)> wrote:
> On Jul 29, 12:40 pm, Cindy Lee <(E-Mail Removed)> wrote:
>
>
>
>
>
> > On Jul 29, 12:24 pm, Cindy Lee <(E-Mail Removed)> 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/asynchr...ure-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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I set my database to trustworthy and deployed this clr stored proc as unsafe ok but when i run this it returns 1 each time. as if ival keeps getting set back to 0 each time i call this clr stored proc. How to fix so that ival keeps its current value DR Microsoft Dot NET Framework 0 30th Oct 2007 12:18 AM
Get return values from a stored proc inside a stored proc =?Utf-8?B?Q2FsdmluIEtE?= Microsoft ADO .NET 2 21st Nov 2005 12:40 AM
Convert VB.NET to TSQL PROC & Reference a Proc from another Proc David Lozzi Microsoft ASP .NET 3 1st Jun 2005 07:35 PM
Can't call Stored Proc... How to trace Flow of Web Application in VB.NET? Mo Microsoft ASP .NET 3 19th Dec 2003 08:03 PM
Problem with Long Stored Proc. =?Utf-8?B?SmVubnk=?= Microsoft C# .NET 3 17th Nov 2003 10:33 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:47 PM.