Search code examples
batch-filescheduled-taskswindows-server-2008-r2sqlcmd

Scheduled Task Runs .bat File with sqlcmd, but Doesn't Update the Output File

My scheduled task (on Windows Server 2008 R2) is "successfully" running each morning, but the output of sqlcmd does not get updated in the output file. The log file changes to show that the batch file ran, but the output file remains the same.

When I run a .bat file with the lines of code below, it works just fine. The .csv file gets updated with the most recent query's results.

sqlcmd -S SERVERNAME -i my_query.sql -s "," -o c:\scripts\My_query\query_results.csv -W -h-1

ftp.exe -s:"c:\scripts\My_query\file_upload.ftp"

Useful Information:

  • I am running the scheduled task as my user which is a server admin and has access to the server in question
  • I have tried giving explicit access to the user for full control of all files and folders involved
  • I have tried running C:\Windows\System32\cmd.exe as the action with the argument /c C:\scripts\My_query\my_process.bat to make sure the entire process runs
  • Task scheduler is working fine for other tasks, but it has this same problem with five similar processes
Solution

  • Are the tasks configured to run whether the user is logged on or not? If so, it will never find your SQL file because the default working directory is SYSTEM32 when you configure a scheduled task that way. You will need to provide the full path to the SQL file.