2

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.

npocmaka
  • 55,367
  • 18
  • 148
  • 187
SSISPissesMeOff
  • 412
  • 1
  • 5
  • 15
  • random number between 0 and 60 `SET /a _rand=%RANDOM%*60/32768+1` – npocmaka Sep 19 '14 at 21:01
  • forgive my ignorance, but i do not understand. how will that command allow me to invoke the windows scheduler monday - sunday between the hours i mentioned above? – SSISPissesMeOff Sep 19 '14 at 21:40
  • `waitfor /t %random% fred` will wait in a batch file a random up to 32000 seconds which is 9 hours. Use `set /a` to scale. – Noodles Sep 19 '14 at 22:27

1 Answers1

1
@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).

npocmaka
  • 55,367
  • 18
  • 148
  • 187
  • I have been writting the output from this script to a log file, and it is looking exactly like what I need. I haven't found any out of bounds time yet. Thank you for your help. – SSISPissesMeOff Sep 24 '14 at 12:13
  • I have been testing this now for 2+ weeks, at first it seemed ideal but then i noticed something. the times started getting screwy. (out of range for my issue) when i run tzutil /g on the server i notice its set to CST. since i do not understand the random time formulas in the answer provided at all, can you please tell me what needs to be adjusted to make this work properly. Also if you wouldn't mind explaining to me the random function for time. and what the numbers signify – SSISPissesMeOff Oct 22 '14 at 20:18
  • @SSISPissesMeOff - `%random%` variable generates a random number between 0 and 32767 .so `!RANDOM!*59/32768+1` generates a random number between `1` and `59` and `!RANDOM!*6/32768+1` between 1 and 6 and `!RANDOM!*7/32768+1` - 1 and 7. So `rand_h` looks overengineered to me (I don't know why I've created this so complicated...).To generate random number between X and Y (X – npocmaka Oct 23 '14 at 06:42
  • with that said, since the server time is set to CST and i want the hours generated to be between 6AM and noon PST I need to set rand_h=!RANDOM!*8/32768+1 and rand_a=!RANDOM!*14/32768+1 how would that translate to the new formula you proposed? – SSISPissesMeOff Oct 23 '14 at 20:36