locked
Start long-running Store procedure from ASP.NET RRS feed

  • Question

  • I have searched this topic to some degree on line and most of the answers I find do not fit my scenario.

    I have an SP that takes some time to execute. The proc populates a table and when it finishes it uses SQL Mail to notify the user of completion.

    I want the user to be able to launch the SP from an ASP.NET page/webpart and be able to close the page and move on to something else.

    Most of the articles I have found speak to using threads and waiting for async call-backs, etc. I do not need that level of complexity. Just want a "start and go" approach.

    Thanks

    Wednesday, February 5, 2014 8:07 PM

Answers

  • You didn't say you needed to pass parameters to the procedure.  :)

    One way of circumventing this problem is by dynamically updating the job step which executes the stored procedure to include the parameters the user provided.  You can do so by using msdb.dbo.sp_update_jobstep.

    So for example, you could create a job named "Test job" and in that job the first step would be to execute your stored procedure.  You would then use the following code to update that step at run time:

    execute	msdb.dbo.sp_update_jobstep
    		@job_name = N'Test job',
    		@step_id = 1,
    		@command = 'execute my_proc @my_variable = ''my_value'''
    You'd then use sp_start_job like so:
    execute msdb.dbo.sp_start_job @job_name = 'Test job'
    Your job would then execute using the value provided at run time for the @my_variable parameter.

    Thursday, February 6, 2014 3:58 PM
  • If you want a fire-and-forget action in ASP.NET you can just kick off a Task (4.5) or use ThreadPool.QueueUserWorkItem (any version) to run a method on a background thread.  Or just spin up a new Thread to run your proc.

    If you do this, your stored procedure will be run on a .NET background thread, so if your AppPool recycles your stored procedure will be aborted.  And you shouldn't use the ASP.NET threadpool for large numbers of simultaneous background tasks.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Elvis Long Wednesday, February 12, 2014 9:55 AM
    • Marked as answer by Elvis Long Tuesday, February 18, 2014 3:17 AM
    Thursday, February 6, 2014 4:38 PM

All replies

  • The simplest approach to this problem for you is probably creating a job to execute the stored procedure.  You could start the job from your web page/part and doing so is simple.  Taken from the Microsoft documentation on sp_start_job:

    EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
    

    See the documentation on sp_start_job here.

    You could also send an email notification from the job alerting the user once the procedure completes.

    • Proposed as answer by Tom Phillips Wednesday, February 5, 2014 8:40 PM
    Wednesday, February 5, 2014 8:25 PM
  • I looked at the link you provided; unfortunately, there doesn't seem to be any way to pass parameters to the stored proc.

    Thursday, February 6, 2014 3:41 PM
  • You didn't say you needed to pass parameters to the procedure.  :)

    One way of circumventing this problem is by dynamically updating the job step which executes the stored procedure to include the parameters the user provided.  You can do so by using msdb.dbo.sp_update_jobstep.

    So for example, you could create a job named "Test job" and in that job the first step would be to execute your stored procedure.  You would then use the following code to update that step at run time:

    execute	msdb.dbo.sp_update_jobstep
    		@job_name = N'Test job',
    		@step_id = 1,
    		@command = 'execute my_proc @my_variable = ''my_value'''
    You'd then use sp_start_job like so:
    execute msdb.dbo.sp_start_job @job_name = 'Test job'
    Your job would then execute using the value provided at run time for the @my_variable parameter.

    Thursday, February 6, 2014 3:58 PM
  • If you want a fire-and-forget action in ASP.NET you can just kick off a Task (4.5) or use ThreadPool.QueueUserWorkItem (any version) to run a method on a background thread.  Or just spin up a new Thread to run your proc.

    If you do this, your stored procedure will be run on a .NET background thread, so if your AppPool recycles your stored procedure will be aborted.  And you shouldn't use the ASP.NET threadpool for large numbers of simultaneous background tasks.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Elvis Long Wednesday, February 12, 2014 9:55 AM
    • Marked as answer by Elvis Long Tuesday, February 18, 2014 3:17 AM
    Thursday, February 6, 2014 4:38 PM
  • I have an SP that takes some time to execute. The proc populates a table and when it finishes it uses SQL Mail to notify the user of completion.

    I want the user to be able to launch the SP from an ASP.NET page/webpart and be able to close the page and move on to something else.

    One method to execute a stored procedure asynchronously without a connected client is with Service Broker.  You can call a proc that writes the needed parameters (typically XML) to a queue.  An activated proc with your code will then get launched to populate the table and send the email.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, February 13, 2014 4:10 AM