Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using WAITFOR and PRINT in a loop does not show any result Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 3:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 7:07 AM
Points: 7, Visits: 54
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?





Post #454922
Posted Wednesday, February 13, 2008 8:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
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."
Post #455134
Posted Tuesday, February 19, 2008 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 7:07 AM
Points: 7, Visits: 54
Thanks!

It works as intended.

/Martin
Post #457318
Posted Wednesday, February 20, 2008 8:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 188, Visits: 424
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.
Post #458014
Posted Wednesday, April 03, 2013 4:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 4:09 PM
Points: 1, Visits: 17
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.
Post #1438612
Posted Tuesday, April 09, 2013 10:56 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #1440645
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse