June 19, 2003 at 8:58 am
I have SP_1 which basically has two EXEC statements which EXEC's SP_2 and SP_3. SP_2 and SP_3 take 10 mins each. What I want is to set SP_2 and SP_3 off but I do not want SP_1 to wait for the SP_2 and SP_3 to finsh before SP_1 finishes. SP_1 and SP_2 can run simultaneously. So I want SP_1 just to take a couple of seconds to run eventhough it has set off SP_2 and SP_3.
Can anyone help
June 19, 2003 at 9:37 am
Build two SQL Server Agent jobs. One that executes SP_2, and the other executes SP_3. Then have SP_1 start the SQL Server Agent jobs by using the sp_start_job SP.
June 19, 2003 at 9:40 am
Of course the only drawback to this would be you could run sp_1 again, until both the sql agent jobs finished.
June 19, 2003 at 4:45 pm
After a little thought 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply