June 27, 2008 at 12:13 pm
I can't understand the behavior of WAITFOR DELAY.
Why this code flows?
WHILE NOT EXISTS (select * from [192.168.81.56].Common.dbo.test where Col1 = 200)
begin
print 'aaaa'
print 'cccc'
print 'dddd'
end
and this doesn't? It's hanging.
It's not doing any output.
WHILE NOT EXISTS (select * from [192.168.81.56].Common.dbo.test where Col1 = 200)
begin
print 'aaaa'
print 'cccc'
WAITFOR DELAY '00:00:05'
print 'dddd'
end
June 27, 2008 at 12:58 pm
Try replacing your "print" commands with "raiserror", and use the "with nowait" option.
raiserror('aaa', 10, 1) with nowait
Print commands often wait till a buffer fills up before they send them to your screen.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 2:10 pm
Great!
It works.
Thank you very much!
July 2, 2008 at 12:32 pm
One more question.
Is this a valid statement?
WAITFOR DELAY '00:10:00' TIME '23:00:00'
What I need is to keep trying to find data
till 11PM. So my code looks like this:
declare @CurrentDt smalldatetime
declare @CurrentDtStr varchar(8)
set @CurrentDt = getdate()
set @CurrentDtStr = CONVERT(VARCHAR,@CurrentDt,112)
WHILE NOT EXISTS
(
SELECT * FROM DB2_PROD_DCDB.DCDB.APP.NIAD_ETL_STATUS WHERE CONVERT(VARCHAR,etl_dt,112) = @CurrentDtStr
)
WAITFOR DELAY '00:10:00' TIME '23:00:00' -- 10 minute delay, timeout at 11 PM
It actually gives me an error:
Incorrect syntax near 'TIME'
It looks like you cannot use both parameters at the same time.
It's either you use "DELAY" or "TIME". But not both.
Am I right?
July 2, 2008 at 1:10 pm
All of this is probably better handled from the client side but YMMV 🙂
* Noel
July 2, 2008 at 1:15 pm
WAITFOR TIME would delay the process till 11 PM.
What you want to do to make it stop at 11 PM is add something to the While statement:
and datepart(hour, getdate()) < 23
That should do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 1:30 pm
Are you suggesting this:
declare @CurrentDt smalldatetime
declare @CurrentDtStr varchar(8)
set @CurrentDt = getdate()
set @CurrentDtStr = CONVERT(VARCHAR,@CurrentDt,112)
WHILE NOT EXISTS
(
SELECT * FROM DB2_PROD_DCDB.DCDB.APP.NIAD_ETL_STATUS WHERE CONVERT(VARCHAR,etl_dt,112) = @CurrentDtStr
and datepart(hour, getdate()) < 23
)
WAITFOR DELAY '00:10:00'
This code is the first step in SQL Server job.
If I just add 'DATEPART(....)" criteria and it's 23:01
and the data is still unavailable it would still wait.
But what I need is to stop waiting at 23:00
and send an email notification "23:00 - data is still not available. Aborting the job"
I actually don't know now how I can stop the job at this point
but I'll find some way. The issue for me is to stop waiting
at 23:00 and notify that I'm stopping
July 2, 2008 at 1:59 pm
I don't have any other solution other than add another job step
"Cancell_If_Time_Passed":
IF datepart(hour, getdate()) >= 23
BEGIN
execute Send_Status_Email 6,0
EXEC msdb..sp_stop_job @job_name = 'NIAD_stop_job'
END
It's too many little steps now in the job
and it's hard to follow the flow but I don't see any other solutions.
July 2, 2008 at 2:31 pm
riga1966 (7/2/2008)
I don't have any other solution other than add another job step"Cancell_If_Time_Passed":
IF datepart(hour, getdate()) >= 23
BEGIN
execute Send_Status_Email 6,0
EXEC msdb..sp_stop_job @job_name = 'NIAD_stop_job'
END
It's too many little steps now in the job
and it's hard to follow the flow but I don't see any other solutions.
I believe that all he was saying is that you ADD the condition in the while loop like:
WHILE NOT EXISTS (...) and datepart(hour, getdate()) < 23
BEGIN
....
END
* Noel
July 2, 2008 at 2:33 pm
I'm talking about something like this:
eclare @CurrentDt smalldatetime
declare @CurrentDtStr varchar(8)
set @CurrentDt = getdate()
set @CurrentDtStr = CONVERT(VARCHAR,@CurrentDt,112)
WHILE NOT EXISTS
(SELECT *
FROM DB2_PROD_DCDB.DCDB.APP.NIAD_ETL_STATUS
WHERE CONVERT(VARCHAR,etl_dt,112) = @CurrentDtStr)
AND datepart(hour, getdate()) < 23
BEGIN
WAITFOR DELAY '00:10:00'
...do other stuff...
END
IF datepart(hour, getdate()) >= 23
BEGIN
... do what you need after 11 PM ...
END
The "Select *...." is separate from the datepart test. That makes it so the While loop will only continue while both are true: the data is missing AND it's before 11 PM.
As soon as one of those is no longer true, either the data exists, or it's 11 PM, it will exit the While loop and move on to what's after that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply