Need advice on a scheduling project with SQL backend

[wey97]

I'm writing a project to schedule a task on certain days of the week at certain times. I have choices of 8:00 am, 8:15 am,.... 5:00 pm and choices for the seven week days.

The problem with SQL is that the datetime field also needs a date, not just a time, but I don't want to have to restrict it to having a set date (unless I picked the minimum (Jan 1, 1753?) as the default date...

One solution that I considered was to fill a table with all the future dates and times possibly available for scheduling like until Dec 31, 9999, but that would be a huge table. This would be easier for showing the next scheduled date/time the task should run but would be hard to set using certain times and days of the week as options.

So my only solution was to have a table with an ID (int) and a varchar(8) to hold the time values and a table with an ID (int) and varchar(10) for days of the week.

The problem comes when I want to show the scheduled date and time when the task is next scheduled to run. What's the simplest way to find this? And then do I store it or just calculate it for every task?

I know this is long, but somebody surely has run into this before. Any suggestions will be appreciated.

[dRAMmer]

I don't quite understand....
Here you are not concerned about saving the date:

The problem with SQL is that the datetime field also needs a date, not just a time, but I don't want to have to restrict it to having a set date (unless I picked the minimum (Jan 1, 1753?) as the default date...

Here you want to have (all possible?) values until Dec 31,1999

One solution that I considered was to fill a table with all the future dates and times possibly available for scheduling like until Dec 31, 9999, but that would be a huge table. This would be easier for showing the next scheduled date/time the task should run but would be hard to set using certain times and days of the week as options.

Is your goal having to run a task scheduled this Monday @8AM for next week on the same schedule?

[szlamany]

You said SQL - so I'm assuming you mean MS SQL Server - right?

Have you looked at the options available in ENTERPRISE MANAGER?

Under DATA TRANSFORMATION SERVICES you create a LOCAL PACKAGE - when you have it all set, you SCHEDULE it.

This schedule is extremely flexible and robust - all the features you want - times, days - everything.

Once scheduled, the PACKAGE appears as a JOB under MANAGEMENT>SQL SERVER AGENT.

The AGENT is responsible for running the JOB - and tracking job logs.

Why are you looking to do this yourself when it's already available?

[wey97]

I don't quite understand....
Here you are not concerned about saving the date:
Here you want to have (all possible?) values until Dec 31,1999

Is your goal having to run a task scheduled this Monday @8AM for next week on the same schedule?

Yes, that's the goal to run the task at 8AM, 9AM, 10AM.... whatever the user picks and on whatever days of the week they pick. With this I need to somehow calculate the next time the task will run. So I have three options: 1) Save all the times with some default date 1/1/1753 and ignore the date part 2) Save the times as a text field only 3) Save all possible date/time combinations for, say, the next couple hundred years (The more I think about it, this is a BAD idea)

You said SQL - so I'm assuming you mean MS SQL Server - right?

Have you looked at the options available in ENTERPRISE MANAGER?

Under DATA TRANSFORMATION SERVICES you create a LOCAL PACKAGE - when you have it all set, you SCHEDULE it.

This schedule is extremely flexible and robust - all the features you want - times, days - everything.

Once scheduled, the PACKAGE appears as a JOB under MANAGEMENT>SQL SERVER AGENT.

The AGENT is responsible for running the JOB - and tracking job logs.

Why are you looking to do this yourself when it's already available?

Yes, MS SQL Server, and the DTS in SQL won't be possible because I have many different clients who have to save options in this remote database and I have to write an application that will be running at all times on their machine to see if the task needs to be run.

[szlamany]

I would save the times as a text field - you certainly aren't getting any gain treating them as a date.

I guess you are planning to somehow save what "days of the week" the job should run also - right?

I do believe you can get at DTS from VB - so maybe your "can't use DTS" and the regular scheduler AGENT issues can be overcome.

[si_the_geek]

Yes, that's the goal to run the task at 8AM, 9AM, 10AM.... whatever the user picks and on whatever days of the week they pick. With this I need to somehow calculate the next time the task will run.

Why not just store the date/time it should next run? (and probably another field to say the number of days to increment after it has run).