| Author |
Topic |
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
 Posted - 2007-06-22 : 16:11:40
|
| I have a SQL Agent Job which has a few steps in it. But I need to schedule it to Run only on Business Days at 9PM.Is there any way to do that in SQL Server.I have a table in my database which has list of Holidays till 2030 but not weekends. I think I can get the list of weekends too but what is the way to run this job only on weekdays.Ashley Rhodes |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2007-06-22 : 16:12:34
|
| i mean BUSINESS DAYS NOT WEEKDAYS.Ashley Rhodes |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-22 : 16:53:04
|
| You may need check your holiday list first in the job, and schedule to run the job in weekdays. If it's holiday, exit the job without doing anything.. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-22 : 20:45:55
|
quote:
I have a table in my database which has list of Holidays till 2030 but not weekends. I think I can get the list of weekends too but what is the way to run this job only on weekdays.
Schedule it to run weekly From Mon to Fri only. Check for holiday that falls in weekdays in your stored procedure.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2007-06-25 : 10:24:32
|
| so can i have that stored procedure as the first step of the job.and if its a holiday then i can quit the job. Say the job name is A1,how would i do that in a stored proc. Any detail would be appreciated. Any samples or examples?Ashley Rhodes |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-25 : 10:26:14
|
| In the SP check the date against the holiday table. If it's a holiday then raise an error. In the job exit the job with success if that step fails.I would also check for weekends.It depends on the business rules as to where you would put this. What happens if the proc doesn't run on the friday (machine down?). If you want to run it on the saturday then it's best to put the check in a separate step. If you don't then put it in the processing proc.You maight also put in a check that it hasn't already run for that day.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2007-06-25 : 10:46:42
|
| This is the stored procedure --------Can you modify it to show me how to go to the next step of how to quit if the day is weekday and a holiday.IF datename(weekday, getdate())IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY')BEGINIF (dateadd(day,(0),datediff(day,0,getdate()))) not in (select holidaydate from holidayTABLE)BEGIN----GO TO NEXT STOP IN THE JOBENDELSE ---------QUIT THE JOB------ENDELSE ---------QUIT THE JOB------Ashley Rhodes |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-25 : 10:55:22
|
quote:
so can i have that stored procedure as the first step of the job.
Why not encapsulate all your other SPs into this SP that will check for weekday, holiday etc ?if <it is weekday > and not <holiday>begin <exec your SPs here> exec sp1 exec sp2 ...end
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Schedule a SQL agent Job to run on Business Days