ADO.NET 2.0 Asynchronous Command Execution: Independant of Web Session?

[amy.yerks]

I have a small ASP.NET 2.0 application that needs to be able to kick
off a long stored procedure (for importing and processing tables ) and
then allow the user to continue with the session, perform other tasks,
and log out without having to wait for the SP to complete. I read the
following article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp

This looks very promising but I could not figure out what would happen
to the thread if the user logged out before it completed. The stored
procedure could take several hours depending on the parameters selected
so I need the command to be independant of the web Session. Will it
keep running or die when the user logs out? If it dies, can anyone
suggest a way to set it up so it will run until complete?

Thanks for your help!

 

[David Browne]

I have a small ASP.NET 2.0 application that needs to be able to kick
off a long stored procedure (for importing and processing tables ) and
then allow the user to continue with the session, perform other tasks,
and log out without having to wait for the SP to complete. I read the
following article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp

This looks very promising but I could not figure out what would happen
to the thread if the user logged out before it completed. The stored
procedure could take several hours depending on the parameters selected
so I need the command to be independant of the web Session. Will it
keep running or die when the user logs out? If it dies, can anyone
suggest a way to set it up so it will run until complete?

Several hours is way too long for Asyncronous Commands or background
threads. If nothing else, the applciation may bounce in that time, in which
case the database server will abort the batch.

If this is SQL Server, perhaps you can set up a SQL Agent job and kick it
off with the sp_start_job stored procedure. The user can check the status
periodically, but the execution won't depend on the web server or the user
hanging around that long.

David

 

[amy.yerks]

Thats the best solution i have heard yet and ive been looking around
all day trying to figure this out. thanks! i can execute a simple SP
to store the params and then run the other to kick off the job.

 

[William \(Bill\) Vaughn]

I've asked a couple of experts at MS about this. I don't see an easy
solution, but I have some ideas that I'm bouncing off them.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

 

[amy.yerks]

Well the sql agent job idea worked. Is there anything similar to a job
in SQL Server but that is created at the database level? This is
eventually supposed to be a turnkey deployment (to be potentially used
in multiple client databases) so if i could get around having a unique
job for each database at the server level it would be easier to add
into a database setup script.

 

[Guest]

If this is SQL Server, perhaps you can set up a SQL Agent job and kick it

off with the sp_start_job stored procedure. The user can check the status
periodically, but the execution won't depend on the web server or the user
hanging around that long.

David

I had this same idea a while ago, but I definitely did now know about
sp_start_job. I was trying to add a job schedule programmatically each time,
and although it appeared to set the schedule correctly, it somehow never
seemed to actually work. I'm definitely going to try the sp_start_job method.

So now the other question is, I need to notify the user when the job is
finished. The only thing I've been able to come up with so far is something
like a select from sysprocesses where name like '[jobname]' (I don't have
access to the code at the moment, but that's the gist of it). Basically I
just have the thread sleep and poll the server with that select every 10
minutes or so to see if that process is still active, but I'm not at all sure
that's a reliable method, especially since I haven't been able to do much
testing without the initial job schedule working.

Do you have any suggestions as to how I can find out when the job completes?