I have a stored procedure, which I execute via an REST-Route. As the procedure will take some time to finish and I don't need the reply: How can I execute the procedure without having to wait for the status 200 answer?
Asked
Active
Viewed 617 times
-2
-
Fire off a separate thread. – Dale K Sep 06 '21 at 09:31
-
Since anything you start from the web context will be cancelled when the HTTP Request completes, invoke it from a SQL Agent job. The route can start the job via sp_start_job. – AlwaysLearning Sep 06 '21 at 09:32
-
I'd like to but due to Express SQL Agent Job is no option. – A-Tech Sep 06 '21 at 09:33
1 Answers
0
Something has to maintain the connection to SQL while the procedure executes, or SQL will assume the client has abandoned the operation. But that doesn't mean the REST client has to be that something. It can be the host of the REST service.
So, instead of doing things:
client sends request API calls database API waits for database response API returns 200 to client
You can do this:
client sends request API returns 202 (202 means "accepted", which is probably more accurate then 200 here) API calls database API waits for database response
Of course, you might be worried that this means the API is still "busy" waiting for the database response. There are basically two ways to handle that.
If you expect the procedure to complete "pretty quickly" (a few seconds, maybe half a minute, depending on how busy your API server is), then that's where async comes in, in which case this answer has you covered and this tutorial will get you started.
If you expect the procedure is going to take a long time to complete, or if your API server is getting hammered too hard, then you would put the data into some kind of high performance queue instead of calling the database, and then have a completely separate process which reads from the queue and calls the database.
allmhuran
- 4,154
- 1
- 8
- 27
-
1Either this is question a duplicate (in which case vote as such), or [your answer is in another castle](https://meta.stackexchange.com/questions/225370/your-answer-is-in-another-castle-when-is-an-answer-not-an-answer) (which makes this not a good answer). – Jamiec Sep 06 '21 at 10:37
-
@Jamiec I disagree that this is a dupilicate answer, because I haven't simply directed the asker to "use async per this answer over here". I've provided a "solution guide", ie, not just the specific answer, but also how to decide whether that answer is appropriate for them, and if not, what the alternative would be. I suppose an argument *could* be made that this answer would also be appropriate as a "super long series of comments but without any of the formatting" – allmhuran Sep 06 '21 at 10:41