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


 


 

Comments (0)