SQL Server Performance Forum – Threads Archive
Run store procedure in backgroundI have an old DB and new DB that are different from table structure, but have a similar content. When a user update old DB through a web application (ASP) (task1), I will run an extra store proc to update new DB (task2). It takes me alot of time. So I would like to run the (task2) background. That means The application will run task1 and receive result, then call task2 but not wait for result ( this task will run background), and continue to running task 3.
We don’t care about the result of task 2. The new DB serves for reporting. User can view report after updating old DB so new DB must be updated immediately after user update old DB.
Please show me how to run the store procedure in task 2 background, or another way to solve this problem. Thank you very much
When you call any sp from application then there is no way to jump to next line till
the result of running the SP has come back.
In task1, instead of calling stored proc 2, update a database entry with all the params it needs to call proc 2 (or if it doesnt take any params, just set a flag to indicate it needs to be run) Then schedule a job to run periodically, which checks this table, and if necessary, can then call proc 2 with the params saved earlier Chappy
But if we do it periodically, user can’t view report immedidately after they update the old database. We must update the new DB (serving for report) as soon as user update old DB. Thank you.
Have you thought about using replication? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Have you considered just putting a trigger on the Old Database. So that as soon as the data is inserted/updated in the table, then you can do whatever you want to with it immediately, regardless of why the data was changed, or what application changed it?