Schedule Batch Execution in 24 hours from Now()

[devRyan]

Hello All,

I've got a rather large DTS App that I've been working on and I'm just about finished with it, with the exception of one problem. I need to schedule a batch execution at some point in the future, as part of my app.

Here's the basics.

I recieve excel and text files from various branch offices once a month containing employee rosters, this information is used to send our updated total roster to a third party firm we use for online training. I import these files into the db, run a comparison of who is still active and who is now inactive. I produce two files, one to activate/update employee info, and one to deactivate employees that are no longer with the company. Everything runs smooth up to this point.

As I produce the files, I also produce batch files that are executed and ftp the roster files to the third parties system. From what I've been told, they will only process 1 file every 24hours.

I've only just recently added the deactivation file to this scheme, so this wasn't a problem. I was able to complete file and batch creation, then get DTS to fire off the batch file. Now I have 2 batchs that need to be sent, one 24hours after the first.....and I have no idea how to set up a 24 hour timer to countdown after the first batch is executed, or if it's even possible.

If you have any ideas on how to do this, please let me know.

Thanks,

devRyan

 

[devRyan]

I've found a solution to this problem. T-SQL has the WAITFOR function that can be used to "wait for" a specified length of time or for a specified time stamp, up to 24 hours in the future.

So far from what I can tell in my testing, the WAITFOR command has relatively low CPU overhead, which is good.

 

[mrdenny]

Using the waitfor probably isn't the best solution as this requires that an open T/SQL connection to the SQL Server. What happens when the SQL Server is restarted?

Look into using the AT command to schedule the remote batch file. This will use the task scheduler.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog