Tab with interrupted query stays unable to re-execute

worldexplorer posted 1 month ago in Running SQL scripts

steps to reproduce:

1) create a long running query

2) run it

3) interrupt by ESC or red X with "Cancel current operation"

4) in my setup, I'm getting a popup window with SQL Server error: "you don't have a permission to execute KILL operation"

log looks like this:

/ [Helper connection] Connecting to MYPRODUCTION via Microsoft SQL Server (TCP/IP), username xxx\xxxxxxxxxx, using password: No ... /

[Helper connection] SELECT @@SPID;

/ [Helper connection] Connected. Thread-ID: 181 /

[Helper connection] SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "master"."dbo"."sysprocesses" WHERE "spid"=1;

[Helper connection] SELECT @@VERSION;

[Helper connection] SELECT SERVERPROPERTY('ProductVersion');

[Helper connection] SET TEXTSIZE 2147483647;

[Helper connection] KILL 65;

/ [Helper connection] SQL Error (6102): User does not have permission to use the KILL statement. /

/ [Helper connection] Connection to MYPRODUCTION closed at 2017-09-18 13:08:44 /

5) I'm clicking to my LOCAL db to run the same query and see why it runs so long

6) but the tab with the query still thinks it's still waiting for the outcome of KILL operation and due to this:

6.1) RUN (green triangle), and Interrupt (red round X) buttons are still disabled

6.2) F9, Ctrl+F9 are unresponsive - nothing happens if I push these buttons.

7) So I have to close the old tab that will never wake up anymore and open a new tab to run the same query that has failed to be interrupted.

I feel that even if the query was interrupted okay (the user has permission for KILL, and the response from the helper connection wasn't an exception) - the tab will still stay frozen with disabled RUN and F9 features

Could you please take a look?

Thanks, Pavel

worldexplorer posted 1 month ago


7) so I can't even close the old tab that will never wake up, and log says:

/ Cannot close tab with running query. Please wait until query has finished. /


Please login to leave a reply, or register at first.