Clever interogation required from a spid

  • I am trying to investigate a looping process and want control over the spid execution process while it is running?

    I have a sproc looping through rows using a while loopcount <= recordcount executing another sproc that checks if a record exists and updates it otherwise inserts it. The table been worked on has triggers logging changes that controls a custom built data distribution process etc. so many changes happening within each loop cycle.

    What I want to be able to do is, while the process is running, create a "pause" in the loop so I am able to investigate details about the process but I don't want the pause to be activated until I request it.

    This "utility" will also prove very useful when investigating processes on our production servers so the "pause" control needs to be external to the process.

    Has anyone heard of or uses a tool that will allow you to pause a spid while it is executing?

  • The quickest dirtiest way I can think of is wrap your sporc with a check. Then create a control table with a bit value. If the value is 1 then have the code do a WAITFOR (see BOL) and check again. If still 1 again WAITFOR (this is a loop) and once no longer 1 then process goes again. That is the only way I know to stage a pause.

  • I thought as much and am in the process of building this functionality into my sprocs that loop execute.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply