Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: pausing stored procedures

    Is there any way to execute a stored procedure, get it's thread id and pause it in someway...particularly in another stored procedure?

    If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.

    You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    trace and profiler?

    Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •