Yet another annoying question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Yet another annoying question

Author

Topic

Ishamael
Starting Member

14 Posts

Posted - 2006-06-18 : 23:26:35

Hey guys, I bet you didn't guess it but I'm posting here because i need help :P

What I'm trying to do is create a procdure that will run a task on the first Working day of the week.
Normally monday but then theres public holidays etc etc.

on a normal week the task runs monday, on public holiday weeks it is tuesday, and christmas even pushs it back to wednesday.

so far im using the dts designer to do this

What I have is:
a table with the last run date(not sure I even need this now)
a table with a list of public holidays(date) along with a year column

a sql statment getting the last ran date and storing it into a global variable glastrundate

a activex vbscript getting the current year, which is then stored into a globalvariable(gcurrentyear)

then gcurrentyear is then used in a select statement to get the public holidays that are in that year, which are then stored to a rowset (gpubdays)
(select pubdays from tbl_pubdays where pubyear = ?)

Now the problem, I am trying to use another axtivex script to check the current date against each row in the global gpubdays. well really I just can't get the for each loop to work, was trying to use it like:
for each row(or pubday) in gpubdays
if day = today then
task fails and will not run the rest of the procedure
end if
next

sorry if it isnt very clear, still kinda new to SQL server

thanks if you help ^_^

ps. please dont go writing all the code for me, just a nudge in the general right direction

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-18 : 23:37:58

You can do everything is T-SQL. It is easier that way
1. you will need to create your own calender table that defines the public holiday etc.
2. Create the stored procedure that perform your required function
3. Have the procedure to check for your condition (holiday etc) and return immediately if it is a holiday
4. Schedule the Stored Procedure daily

Ishamael
Starting Member

14 Posts

Posted - 2006-06-19 : 00:19:20

thanks,
but how would I force it to only run on mondays when its not a holiday..
it can only run once a week and thats on the first working day.

hate being a pain but is it posible to do it my way at all? would rather be able to just have a small table that could contain the public holidays for the next say 5 years without having 1826 rows or something. also makes it simpler for others to modify it.

if its really not posible i guess i can start on that way

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-19 : 00:29:49

"but how would I force it to only run on mondays when its not a holiday..
it can only run once a week and thats on the first working day."
Schedule it daily and perform the checking in the Stored Procedure.

if Monday and NOT holiday
begin
run your query
end
else
begin
return
end

Ishamael
Starting Member

14 Posts

Posted - 2006-06-19 : 00:31:36

Ok I might have it now. Hopefully this will work, thanks for helping khtan ^_^ might try that way 2 to find which is more efficent

Function Main()
'declarations
dim objLastrun
dim objPubdays

'seting to globalvariables
Set objLastrun = DTSGlobalVariables("glastrundate").Value
Set objPubdays = DTSGlobalVariables("gpubdays").Value

Do While Not objPubdays.EOF
'code should be in here
msgbox (objPubdays.Fields.Item(0).Value)
objPubdays.MOVENEXT
Loop

Main = DTSTaskExecResult_Success
End Function

Ishamael
Starting Member

14 Posts

Posted - 2006-06-19 : 00:49:06

true, but if it fails to run on the monday it must then run on tuesday. then failing tuesday(say boxing day holiday just after christmas) it has to go on wednesday. and theres always a chance someone would turn it on manually, even if that happened on friday it would still need to happen again on the first work day the next week. bah I would rather pay some kid $20 a day to click the button

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources