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