INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Stop Endless Loop

Stop Endless Loop

(OP)
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.

RE: Stop Endless Loop

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

Borislav Borissov
VFP9 SP2, SQL Server

RE: Stop Endless Loop

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

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

RE: Stop Endless Loop

rofl

Borislav Borissov
VFP9 SP2, SQL Server

RE: Stop Endless Loop

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.

RE: Stop Endless Loop

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....

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close