T-SQL background processing
I am having trouble finding the wording, but is it possible to provide a SQL query to an MS SQL server and get the results asynchronously?
I would like to send a request from a web request, but I want the web process to terminate while the SQL server continues to process the request and dumps the results into a temporary table that I can retrieve later.
Or is there some generic modifier I can add to the query to make it background-process the results (like "&" in bash).
Additional Information
I run a site that allows trusted users to run arbitrary queries on very large datasets. I am currently using the Java Daemon to examine the "jobs" table and run the results, I just hope there may be a more custom solution.
It really depends on how you are communicating with the DB. With ADO.NET, you can execute command execution asynchronously. If you want to do this outside of any library built to do this, you can insert a record into the jobs table and then poll the SQL agent agent, and then run your job as a stored procedure or whatever.
In all likelihood though I would assume your web request is received by asp.net and you can use the ADO.NET classes.
source to share
See this question Start stored procedures sequentially or in parallel
Basically, you have to launch the web page. The work was done asynchronously.
source to share
Since http is connectionless, the only way to associate a search with a request is through sessions. You will have all these answers waiting for someone to demand them, and will not be able to know if the connection (which does not exist) has been broken.
On the webpage, this uses-it-or-lose-it pretty much.
Some of the other answers might work a lot, but I don't understand you are looking for a concise, high-tech option.
source to share
This is a complex topic that allows you to execute a stored procedure and then retrieve the result asynchronously. This is not entirely for the faint of heart, and my first recommendation was to rethink your design and make sure you actually need to process your request asynchronously in the data layer.
Depending on what exactly you are doing, you should take a look at 2 technologies ... SQL Service Broker which basically allows you to query and receive responses asynchronously. It was introduced in SQL 2005 and sounds like it might be the best choice out of the way you phrased your question.
Take a look at the tutorial for the same Database Services Brokerage Conversations on MSDN: http://msdn.microsoft.com/en-us/library/bb839495(SQL.90).aspx
For longer or larger processing tasks, I'll potentially be looking at something like Biztalk or Windows Workflow. These frameworks (they are much the same, they are from the same team in MS) allow you to run an asynchronous workflow that may not come back for hours, days, weeks, or even months.
source to share