Tuesday, June 10, 2014

SQL Server Using WAITFOR with a loop to run batch at a certain time with loop every (n) seconds

BEGIN
declare @sd datetime,@ed datetime

    WAITFOR TIME '04:28';
Set @sd = getdate()
select getdate()
Print 'Startdate ' + convert(varchar(20), getdate(), 120)
WHILE 1 = 1
BEGIN
Declare @msg varchar(500), @cnt int
select @cnt = count(*) from MyTable (nolock)
Set @msg = 'INFO: MyTable Count = ' + cast(@cnt as varchar(50)) + ', at ' + convert(varchar(120), getdate(), 120)
if (@cnt > 0)
execute dbo.qry_LogEvent @msg, 7, 750
else
execute dbo.qry_LogEvent @msg, 7, 751
delete serviceeventlog where eventid in (750, 751) and createddatetime < Dateadd(hour, -12, getdate())
WAITFOR DELAY '00:00:01'


if (datediff(second, @sd, getdate()) > 12)
begin
print 'break'
break
end
else
continue
End
Print 'Done ' + convert(varchar(20), getdate(), 120)
select getdate()
END;

No comments:

Post a Comment