Rick Caminiti dotCOM

I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour.  I needed it to run at a specific time and to execute in 10 minute incriments and stop .  I was hoping to log the results to a file, but this does just as well.  When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.

This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count.  You could just as easily use a counter and have it execute a certain number of  times.

–don’t even start until 11pm

WAITFOR TIME ‘23:00:00.000′

declare @endtime datetime

set @endtime = cast(‘2009-05-09 00:00:00.000′ as datetime)

–stop at midnight

while getdate() < @endtime

begin

      –every 10 minutes, execute

      WAITFOR DELAY ‘00:10:00′;

      select getdate(), count(*)

      from tablename

      where datetimecolumn > ‘5/7/2009′

      and datetimecolumn < ‘5/8/2009′

end

Kudos to Jerry for helping me with this.