13

I have an opposite problem to Does disconnecting the network stop a query?. I would like to launch a query and disconnect without waiting for results but I don't want the query to stop. Is it possible?

Improve this question

3 Answers 3

Reset to default
26

As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages).

You have a few options to maintain the connection/session for a long-running query, with and without an interactive client applciaton.

RDP into the SQL Server machine or jump box and execute the query from there. This will allow you to disconnect from the RDP session while the client application (e.g. SSMS, SQLCMD, ADS, etc.) continues to run. You can later reconnect to the same Remote Desktop session to review the results.

Create a SQL Server Agent job with a T-SQL, PowerShell, CmdExec, or SSIS job step to execute the query without an interactive client.

Create a Windows Task Scheduler task to execute the query, using SQLCMD, PowerShell, or other command-line tool.

Encapsulate the query in a stored procedure and execute it as a Service Broker activated proc. This requires a Service Broker enabled database and the proc code will need to capture results. See Asynchronous procedure execution for code examples of this technique and other considerations.

Without an interactive client, results and messages that won't be visible so make sure these are logged/captured according to your needs. Also ensure command-line applications return a non-zero exit code after errors so it's reported as an error by SQL Server Agent or Windows Task Scheduler.

Improve this answer
1
  • One additional benefit of an agent job, you can also set it to delete the job after it succeeds\completes in the Notification section, that way cleaning up after itself.
    – BCM
    Commented Aug 31, 2020 at 16:17
12

Not directly using normal tools (SSMS etc.) and access libraries - if you disconnect progress should stop and any active transactions will get rolled back.

You could run the statements via an agent job set to start immediately, assuming that this is configured and your login has sufficient privileges.

Improve this answer
1

This answer is very similar to Dan Guzman's, but it does differ slightly. RDP into an always on box for which you have rights and submit for execution from there. Always on box can then raise some sort of semaphore or do some further processing. Use Task Scheduler if you have to have a client submit request and then you don't even have to RDP anymore.

Apologies to Dan if I'm confusing jump box and just reiterating his point.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.