Stop Endless Loop

[grnzbra]

I have a stored procedure, in SQL Server 2012, containing a cursor that doesn't have the second FETCH statement, so @@FETCH_STATUS is always zero and it keeps running and running and running. Is there any command that can be issued that will tell it to STOP NOW? I've tried canceling the procedure and there is a notice at the bottom of the query window that says "Cancelling query..." but nothing seems to be happening.

 

[bborissov]

Why this loop does not have second FETCH?
Use BREAK to exit the loop.

Borislav Borissov
VFP9 SP2, SQL Server

 

[grnzbra]

Because the idiot (me) forgot to put it in.

But it looks like the BREAK would have to already be in the code.

 

[bborissov]

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

So, it looks like the server is stuck now and you have to stop and restart the SQL Server Service to make this stop.

Even if the service is still responsive I don't know a way to stop a certain blocking SPID from outside.

You can list running processes via exec of sp_who and sp_who2, for a short explanation of SPID also see

http://sqlserverplanet.com/dba/spid-what-is-it

Bye, Olaf.

 

[Olaf Doschke]

Oh, it's simpler than I thought, knowing which SPID is the one executing your endless running loop you can use the KILL command:

https://msdn.microsoft.com/en-us/library/ms173730.aspx

If you can't identify it might help to store the sp_who2 result, start the proc once more and see what new SPID is added, when you redo sp_who2 and compare previous and current result. Comparing old and new sp_who2 records and may identify which one is new and which of the old ones is similar and belongs to the initial call.

Bye, Olaf.