Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sq server - wait for

Author  Topic 

xpandre
Posting Yak Master

212 Posts

Posted - 2013-04-27 : 02:20:54
Hi Guys,

I need to have a script such that:

Start at 5 PM


Loop
if time > 8PM, then stop for 1 hour -- (need to do no processing between 8 to 9 PM)
else if time > 5 AM exit the loop -- business hour start - stop all processing
else
process 1000 records
wait for 6 mins
end loop


Could someone help me with this?

Thanks
Sam

xpandre
Posting Yak Master

212 Posts

Posted - 2013-04-27 : 02:33:30


declare @countt int = 1
while (@countt > 0)
begin
if DATEPART(HOUR, GETDATE()) = 20 --warehouse cutoff time
WAITFOR DELAY '01:10:00'--wait for 1 hr

if DATEPART(HOUR, GETDATE()) = 5
set @countt = 0
else
begin
exec @countt = process 5000
WAITFOR DELAY '00:06:00'--wait for 6 mins
print '5000 processed'
end
end
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:57:53
no need of writing wait logic inside code like this

you could simply create a script for doing processing and then schedule it in sql aerver agent.
The schedlue should be given as hourlyand put start and end time as 8 PM and 5 AM next day

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -