CODEWITHSUNDEEP
sql-server
Craig Celeste
Craig Celeste

Reputation: 12653

Continue to run a SQL query after the connection closes

In SQL Server, is it possible to call a long-running stored procedure, close the connection and let it finish? I don't care about the result. Essentially I want to say "Hey SQL, do this work on your own server."

I want to avoid having to make some service to sit with the open connection and wait for it.

Upvotes: 4

Views: 1456

Answers (2)

Jason Cumberland
Jason Cumberland

Reputation: 992

You could also use a SQL Agent job to run the procedure that has no schedule, then issue a SQL command to start the job with "EXEC msdb.dbo.sp_start_job @job_name = ''" If you have to change the command frequently it may not be ideal, but that can be done through SQL calls if needed.

Upvotes: 1

Yuck
Yuck

Reputation: 50855

You'll want to use BeginExecuteNonQuery(AsyncCallback, Object), and then have a callback method that effectively does nothing. It's worth mentioning that the connection won't actually be closed, however.

MSDN SqlCommand.BeginExecuteNonQuery Method

Alternatively, you could use Service Broker to queue the request to run a stored procedure. There's a fair bit of plumbing to set up, though. It might not be worth it in your situation. Especially if you aren't using Service Broker for anything else. The advantage here is that the connection can be closed immediately after queuing with Service Broker, and placing messages into a queue is a very quick operation.

Upvotes: 2

Related Questions