June 27, 2003 at 1:59 am
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.
June 27, 2003 at 2:51 am
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
June 27, 2003 at 2:51 am
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.
June 27, 2003 at 3:06 am
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
June 27, 2003 at 7:58 am
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
June 27, 2003 at 11:52 am
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