I'm trying to make a loop that waits for 5 seconds between every execution.

When Using the following script, which is a bit simplified just to make the example, I do not get any results/Messages from the Query Analyzer

while 0=0begin print 'Martin' waitfor delay '00:00:05'end

But when using the following , I get messages

while 0=0begin print 'Martin' --waitfor delay '00:00:05'end

I have tried to found out the nature of the WAITFOR statement and found the following in the BOL:

"The disadvantage of the WAITFOR statement is that the connection from the application remains suspended until the WAITFOR completes. WAITFOR is best used when an application or stored procedure must suspend processing for some relatively limited amount of time. Using SQL Server Agent or SQL-DMO to schedule a task is a better method of executing an action at a specific time of day"

The previous poster is correct about using RAISERROR as opposed to PRINT to eliminate output queing, however, RAISERROR has a feature that my cause you problems if you are not aware of it. The output string uses a format similar to the C printf function in that it supports % format strings. If you don't escape your use of % signs the RAISERROR call will fail. You can do this by doubling up all % signs in your original string before outputing it.

The following will fail...

Declare @Msg VarChar(8000)Set @Msg='''%'' is an invalid input.'RaisError(@Msg,0,1) with nowait

You should keep in mind that WAITFOR gets confused at midnight. If you ask it to delay for 5 min at 2358 it will never return. Likewise, if you start a job at 1600 and ask it to waitfor 0100, it will wait forever.

Peter E. Kierstead (2/20/2008)The previous poster is correct about using RAISERROR as opposed to PRINT to eliminate output queing, however, RAISERROR has a feature that my cause you problems if you are not aware of it. The output string uses a format similar to the C printf function in that it supports % format strings. If you don't escape your use of % signs the RAISERROR call will fail. You can do this by doubling up all % signs in your original string before outputing it.

The following will fail...

Declare @Msg VarChar(8000)Set @Msg='''%'' is an invalid input.'RaisError(@Msg,0,1) with nowait