none
Task Scheduler sqlcmd with %date command no output and no error log

    Question

  • Dear all, 

    I am a newbie, and i hope i got the right forum for sqlcmd in task scheduler. We are using Server 2008, SQL Server 2008 Express, and are using Windows Task Scheduler for scheduling an Excel output for research purposes. The following string works in command prompt with csv created, but no csv file when scheduled as an action in Task Scheduler.  

    sqlcmd -i "E:\ReportScript\scripttest.sql" -o "E:\Reports\scripttest%date:~-10,2%%date:~-7,2%%date:~-4,4%.csv" -W -s ","

    There is no error code and there is no output file from Task Scheduler. I get the following in the history: 

    Task Scheduler successfully completed task "\Test Reporting" , instance "{4e52e7ab-7019-44e2-8479-827d28d17748}" , action "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.EXE" with return code 1.

    I am using the same login ID for the task scheduler as my command prompt. If i leave out the various %date parameters in the action, the task scheduler runs and gives an output file scripttest.csv without problems. How can i rephrase the %date such that task scheduler can accept? 

    Monday, July 16, 2012 12:43 PM

Answers

  • Hi pheng0711,

    Base on my research, when you run the string in command prompt, the %date will be parsed by command prompt first, and obtain the current date, then it was created in the csv file name. But when you run this in Task Scheduler, there is no mechanism like that, so the csv file cannot be created.

    I find a workaround maybe you can try. Please follow the steps as below:

    1. Create a new bat file
    2. Type the string which you use and save the bat file
    3. In the Task Scheduler, right-click your task name, click Properties, click Actions tag, then click Edit, select Start a program as Action, and click Browse, select your bat file, click OK.

    I did a similar test on this. Please see the following screenshot as reference:

    Then you can run the task, and would find that the csv file can be generated successfully.

    Best Regards,

    Ray Chen

    Tuesday, July 17, 2012 5:42 AM
    Moderator

All replies

  • Hi pheng0711,

    Base on my research, when you run the string in command prompt, the %date will be parsed by command prompt first, and obtain the current date, then it was created in the csv file name. But when you run this in Task Scheduler, there is no mechanism like that, so the csv file cannot be created.

    I find a workaround maybe you can try. Please follow the steps as below:

    1. Create a new bat file
    2. Type the string which you use and save the bat file
    3. In the Task Scheduler, right-click your task name, click Properties, click Actions tag, then click Edit, select Start a program as Action, and click Browse, select your bat file, click OK.

    I did a similar test on this. Please see the following screenshot as reference:

    Then you can run the task, and would find that the csv file can be generated successfully.

    Best Regards,

    Ray Chen

    Tuesday, July 17, 2012 5:42 AM
    Moderator
  • Hi Shulei, 

    Thank you for your explanation. Now its finally functioning. Both .cmd and .bat works :-) 

    Pheng

    Tuesday, July 17, 2012 7:43 AM