SYSK 143: Async Invocation of Stored Procs from SQL
If you need to call a stored proc that may take a while, and you don’t want to block on it, your option is to call it asynchronously. In SQL 2005 you can take advantage of the Service Broker… But if you need to do it from SQL 2000, or you just don’t want to use Service Broker, you could use the following code for async. invocation (copied from http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2):
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @osql_cmd varchar(1000)
-- create shell object
exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
set @osql_cmd = 'osql -E -dYourDatabaseName –SyourServer\YourInstance
-Q"YourStoredProcName"'
-- submit
exec @rc=sp_oamethod @object,
'run',
null,
@osql_cmd
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
-- destroy shell object
exec sp_oadestroy @object
