2

I have an ASP.NET web application that needs to submit a long running SQL stored procedure with parameters. It could run up to 30 minutes. How can I submit to run on the sql server as a job so the web app can continue on with something else? Is it possible to check if the job is finished from ASP.NET?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
user2369812
  • 779
  • 2
  • 9
  • 21
  • You can use an async method and the await keyword. – Kfir Guy Apr 14 '17 at 12:57
  • 1
    I usually create a backgroundworker and put sql iinto the backgroundworker. The SQL Server command with SQL.CMD which is a commnad line query to database. I often create a process to run SQL.CMD and put results into a csv file. Then later read csv file into c#. SQL.CMD is more efficient than running a query through c# interface. – jdweng Apr 14 '17 at 12:58
  • read about Quartz.NET or HangFire – MistyK Apr 14 '17 at 12:59
  • 1
    I thought it would be better to run as a sql job because it would continue to run even if the user shut down the browser or the app pool recycled. – user2369812 Apr 14 '17 at 13:01
  • A possible solution is to look at [QueueBackgroundWorkItem](https://blogs.msdn.microsoft.com/webdev/2014/06/04/queuebackgroundworkitem-to-reliably-schedule-and-run-background-processes-in-asp-net/) Although long running tasks in IIS are problematic due to AppPool recycling. – Todd Apr 14 '17 at 13:30
  • You would probably be better served decoupling these long running processes from your application. One option would be to queue up a sql job, however, if this is going to be a common occurrence in your organization or application then you may want to look into a service type application built specifically to handle your common occurrences on a regular basis. That way you could just query a log table to see when the next occurrence of your "service job" will run and what the last result was. – Ross Bush Apr 14 '17 at 14:26

1 Answers1

0

I would handle this by putting the query into a job. Then the page/website would check back to see if the job is complete.

Start a Job How to start SQL Server job from a stored procedure?

Check Status of Job How can I determine the status of a job?

Community
  • 1
  • 1
Jason Geiger
  • 1,912
  • 18
  • 32