October 23, 2003 at 10:06 am
I have a stored procedure on a large SQL Server 7 database that extracts data to a remote SQL7 database. When that process completes, I want to trigger the run of a long stored procedure on the remote server - but I do not want my home server to wait for it to complete. Any suggestions would be appreciated.
October 23, 2003 at 10:27 am
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 -d<db name here> -S<servername here> -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
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 23, 2003 at 9:01 pm
What I typically do in this case is to have a job that queries a table on the remote server. If there is a record in the table that needs processing the job starts the process and updates the record with it's current status. When the process is complete it then updates the status as being completed. If the process errors out it is also updated with an error flag.
I've been using this system to update snapshots automattically when a certain event happens in my publisher database for the last year. It works very well 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 24, 2003 at 1:49 am
Thanks to you both. gljjr, looks a robust solution but I would prefer to avoid polling if possible. Greg, I will need to study this and try it out but it looks like exactly what I was after. Thnaks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply