Argthtjtr

I am in the process of inserting some data into a SQL Server (2016). Since this is a manual one-time load, I have not bothered spending a lot of time writing it the most efficient way; however some of the loads takes much longer than initially anticipated - so here is my question.

Can I somehow tell the server to execute a nother query immediately after the current one finalises? I would have done this manually by hitting F5 on the next part of the code - but since the load takes longer than expected, I would like the server to somehow execute the next part of the code automatically, when the first part finalises.

Is this at all possible without either 1) waiting for the current query to finalise or 2) cancelling the current query and re-running the whole lot at once including my next steps of my code?

Hope this makes sense!

Thanks :-)

For your specific situation: no, there's not a straightforward way to do what you want to do.

I would have done this manually by hitting F5 on the next part of the code...

From this I gather you have one big script with many statements, and you're running it one statement at a time by highlighting the statement and pressing "F5."

Is this at all possible without either 1) waiting for the current query to finalise or 2) cancelling the current query and re-running the whole lot at once including my next steps of my code?

No, that SSMS window (session) is tied up running that query, and you can't use it again until either the query finishes, or you cancel it.

There are ways that you could try to monitor for that session going idle (with a SQL Server Agent job for instance), and then run another query. But it's probably not worth the effort and risk of getting it wrong.

So I would just wait until the current query is finished, and then you can highlight the rest of the script and run it - each statement will automatically run one after the other.

Assuming it is just a single statement that you are waiting to complete, you can first run the following to get the start time of the current request

SELECT start_time FROM sys.dm_exec_requests WHERE session_id = <<spid_of_other_connection>>

And then having got the start_time do some simple polling in another SSMS window

SET NOCOUNT ON;



WHILE EXISTS

(SELECT *

        FROM    sys.dm_exec_requests

        WHERE   session_id = <<spid_of_other_connection>>

                AND start_time = <<start_time_from_above>>)

BEGIN

  WAITFOR DELAY '00:00:10'

END



PRINT 'DO SOMETHING HERE'; 

The filter on start_time rather than just session_id is because the session_id can be recycled if the original connection is closed.

A possible solution would be to open up another SSMS window and put at the very top a very specific update against the table you're trying to load - a row that you know is going to be locked for the entire run of the first query - until the current locks are released, the second session will wait - then follow the update with your second query. – Scott Hodgin