CODEWITHSUNDEEP
sql-serverbatch-filescheduled-taskswindows-task-scheduler
SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

Windows scheduled task / sql server random time

I have the following situation I am working on with the majority of the issue solved. I currently the following folders and files existing on a machine. 

C:\foo\foo.sql
C:\foo\foo.bat

"foo.sql" - is a script file that has a very simple line in it:

EXEC uspUpdateCounts

"foo.bat" - invokes SQLCMD to call execute foo.sql and log any output from the stored procedure called to a text file with the current time and date stamp (this will only run once per day):

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S SvrNme -E -d dbName -i C:\foo\foo.sql -o "C:\foo\fooOutput_%date:~-4,4%%date:~-10,2%%date:~-7,2%.txt"

I am currently using a windows scheduled task to kick off foo.bat I would like to know if there is a way I can script the time to happen randomly every day to run between 6AM and 1PM PST. I have messed around with some time components in scripts I have found here and there, but I cannot get it to randomize the minute. I can get the hour but its not very reliable but at this point I am completely lost. If someone can help me with this solution and also explain the solution, that would be great. I am not just looking for the answer but to learn as well.

Upvotes: 2

Views: 860

Answers (1)

npocmaka
npocmaka

Reputation: 57252

@echo off

setlocal enableDelayedExpansion
for  %%# in (MON,TUE,WED,THU,FRI,SAT,SUN ) do (

    rem clear old tasks

    SCHTASKS /Delete /TN DailyRandom%%# /f >nul 2>&1

    rem random minute between 0 and 60
    SET /a rand_min=!RANDOM!*59/32768+1
    rem random hour between 6 and 13
    set /a rand_h= !RANDOM!*6/32768+1
    set /a rand_a=!RANDOM!*7/32768+1
    set /a rand_h=rand_h+rand_a
    if !rand_min! LSS 10 set "rand_min=0!rand_min!"
    if !rand_h! LSS 10 set "rand_h=0!rand_h!"
    SCHTASKS /Create /SC weekly /D %%#  /TN DailyRandom%%# /ST !rand_h!:!rand_min! /TR C:\foo\foo.bat
)

Not tested and not sure which of all SCHTASKS you'll need (check it's help).And you'll need to run the script every week.It creates a DailyRanomMON,DailyRanomTUE...,DailyRanomSUN tasks that will be set between 06:00 and 13:59 (may be you'll need to tune the numbers).

Upvotes: 1

Related Questions