SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using WAITFOR and PRINT in a loop does not show any result


Using WAITFOR and PRINT in a loop does not show any result

Author
Message
martin.thorin
martin.thorin
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 57
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?
David Jackson
David Jackson
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1478 Visits: 2019
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


http://glossopian.co.uk/
"I don't know what I don't know."
martin.thorin
martin.thorin
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 57
Thanks!

It works as intended.

/Martin
Peter E. Kierstead
Peter E. Kierstead
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 453
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...



PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
John Caraway
John Caraway
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 90
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.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25927 Visits: 12473
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search