something similar to forking a separate process

  • I have a sp that runs for couple of mins / hr depending on the records to process. there is a web page that has to execute this sp and it should not wait for the results to come back from the sp. the web page should only start the process as the user should not be on the same page for a long time waiting for the results. I wanted to find out if there is any way we can just fork a process from a sp.

  • One way to do this is to use a job. The user would initiate the job which would complete the user's transaction but the job would continue until it has completed.

    There may be some permission problems and you might have problems if the user tries to start the job whilst it is still running. One way round this is to put the request into a table. The job runs every minute checking the table and only continues if there is a record in the table.

    If it is something that should occur regularly, you could set up a job to execute every hour on the hour.

    Jeremy

  • I would try using a DTS task for this. The ASP page can then trigger the DTS task, for which you don't have to wait until completion.

    Another way is to embed your call in your own executable that you initiate using xp_cmdshell. Seems like a lot of trouble to me.

  • I can think of two possible strategies for a start:

    the first is to use SQL Agent as an out-of-process server. Either set up a standard job beforehand (any parameters needed could be stored in a table), and just call msdb.sp_start_job, or if you need more flexibility or there is a risk of the job being called a second time while still running, create the job on the fly (perhaps in a stored proc, using parameter values):

    msdb.sp_add_job

    msdb.sp_add_jobstep

    msdb.sp_start_job

    msdb.sp_delete_job

    This way you can give the job a unique name, e.g. by using the @@spid function.

    The second method assumes you are using ADO in ASP for your web data access. In this case, you could 'execute' an ADO 'command' object using the ExecuteOptionEnum values adAsyncExecute (0x10) or adAsyncFetch (0x20).

    Of course with VBS late binding ('createobject()' rather than VB: 'Dim ...as New ...'), you can't generally refer to these constants by name in ASP, but need to use the numeric values.

    The only problem with this approach is that the connection is tied up (unusable) until the command has finished executing. So you might need to make an extra connection to the db to run the async command.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • I think you can use the WSCRIPT SHELL object to do asynchronous processing of SP's. Here is a simple example to demonstrate what I am talking about. This example creates a sp, usp_wait. That waits 2 minutes then creates a table.

    This script then execute usp_wait, using an osql command processed using WSCRIPT.SHELL object through OLE Automation. The script completes right away. If you watch in EM after the scripts completes, you will see the table show up two minutes later.

    create proc usp_wait

    as

    waitfor delay '00:02:00'

    create table abc (a int)

    go

    Declare @rc int

    DECLARE @object int

    DECLARE @src varchar(255)

    Declare @desc varchar(255)

    EXEC @rc = sp_OACreate 'WScript.Shell', @object OUT

    print @rc

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    EXEC @rc=sp_OAMethod @Object,'run',null,'osql -E -dtest -Sesp--gal0303 -q"usp_wait"'

    IF @rc <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

    SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc

    RETURN

    END

    -- verify table abc show up after 2 minutes then drop table and proc

    -- drop table abc

    -- drop proc usp_wait

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • A real low-tech way would be similar to the method mentioned by stax68. Create a stored procedure to do the work. Set it as a job to run every minute (or whatever frequency seems necessary). The arguments indicating that the process should be run would be entered into a table from your calling program. When the job wakes up, it determines there is a new set of criteria and executes. If nothing is new, it just goes back to sleep.

    Guarddata-

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply