Execute Stored Procedure Y asynchronously from Stored Proc X using SQL Server 2000

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

When you say that you want to return from the SQL server execution.Really all of the Execution has to be completed before that Can Happen.When I say all of the Execution I mean both the X and Y[ that is Y and X internally called by Y].Am not sure that why you want the code to be executed this way but try out following list of possible case that can handle the requirement you have.

1. Stored Procedure X

Statement 1

Statement 2

Call to Y

Statement 4

Statement 5

Split the above code to three ... Statement 1 and 2 be in a single procedure . CAll it , once after execution return ...

from the code , again from the call the SP Y async.

sql API now support Async Calls ..hope your are using that ... BeginXXX and EndXXX pairs

|||

My requirement is little bit different man. I want to do lot of work in backgroud.

when i call a stored proc X then it should execute few lines of code and call Stored Proc Y in background. Once stored proc X call stored proc Y then stored proc X should return me result set (i mean execution of X is done) without waiting to get it complete stored Proc Y because it is running in background.

how can we do it? pls suggest me some solution.

You can suggest me solution either in ASP.NET 2.0 OR Sql Server 2000.

|||

Not very sure, but some ppl said it can be down using OLE automation . Could you please check this article?

http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2

Changing Stored Procedure to Submit Code Asynchronously

Now that you understand the initial performance problem with SP "usp_enter_order," let me discuss how I could re-write this SP to submit the slow code asynchronously. First, I will need to create a "new" SP that contains the slow code. The second thing will be to replace the slow code in "usp_enter_order" with some OLE Automation that submits the "new" SP asynchronously. Below is the code for the new SP, I called it "usp_run_slow_code":

Hope my suggestion can help