Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Usin WaitFor Delay in While Loop

    I'm trying to use WAITFOR DELAY statement in a while loop, but it dose not work. I wrote the following code in query analyzer:
    While 1=1
    Begin
    select dbid, count(dbid) as count
    from sys.sysprocesses
    group by dbid
    waitfor delay '00:02:00'
    End
    Is there anything wrong? Please help me.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, that depends. This code may not work on SQL 2000, but it is had to say from just the code, and no error message,or other indication of a problem.

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    I want the loop to run indefinitely and perform the SQL statements periodically. I can get the WAITFOR DELAY to work fine as long as it runs only once but it does not work in the loop.
    Nothing is printed and if I cancel the query it simply reports that user canceled the query and I'm working with SQL 2005.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why aren't you running this as a scheduled job?
    An infinite sproc is not good architecture.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Nothing is printed, because the data returned has not yet hit the network packet size (it is a VERY bad idea to change that for this purpose, by the way). If you redirect the output to a table, you will see the rows generated there.

Posting Permissions

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