February 13, 2008 at 3:07 am
Hi,
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=0
begin
print 'Martin'
waitfor delay '00:00:05'
end
But when using the following , I get messages
while 0=0
begin
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"
Is there any other way to make the loop wait?
February 13, 2008 at 8:45 am
It's not the WAITFOR that is the problem, it's the Print statement.
Try
while 0=0
begin
raiserror ('Martin',0,1) with nowait
waitfor delay '00:00:05'
end
HTH
Dave J
February 19, 2008 at 5:28 am
Thanks!
It works as intended.
/Martin
February 20, 2008 at 8:27 am
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
Corrected version...
Declare @Msg VarChar(8000)
Set @Msg='''%'' is an invalid input.'
Set @Msg=Replace(@Msg,'%','%%')
RaisError(@Msg,0,1) with nowait
You could write an OUTPUT procedure to handle these details for you...
April 3, 2013 at 4:45 pm
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.
April 9, 2013 at 10:56 pm
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
Corrected version...
Declare @Msg VarChar(8000)
Set @Msg='''%'' is an invalid input.'
Set @Msg=Replace(@Msg,'%','%%')
RaisError(@Msg,0,1) with nowait
You could write an OUTPUT procedure to handle these details for you...
There is a simpler way:
RaisError('%s',0,1, @Msg) with nowait
No need for any escaping.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply