Running a stored procedure using the Windows Task Manager Scheduler

Question

I tried to set a schedule to run the stored procedure every hour in the Windows task scheduler (since I use SQL Express and cannot install third-party tools), but after you tried to use various methods, such as starting .bat from the task scheduler, opening the utility SqlCmd from task scheduler and passing command line syntax or .sql script file. I was not lucky.

I know that this can be done, and therefore I am sure that this is what I missed, but if someone can share their experience, I would really appreciate it.

Thank you so much

Answer 1

If you are the sql instance administrator (since you are using SQLExpress, I'm sure you are trying to do this on your own computer, so there is a high chance that your user is the sql instance administrator), you should not use -E at all, just ignore it.

Secondly, specify the server, even if you are working with a local one.

Start with a simple sql command as shown below:

sqlcmd.exe -S "." -d MY_DATABASE -Q "SELECT * FROM MY_TABLE"

Replace MY_DATABASE and MY_TABLE with the name of your dbname and table. Make sure you can run it from the command line. It should return data from your table. (Optional command line options are case sensitive, so -s does not match -S)

Finally, do not try to pass parameters through the task scheduler. Put the command with all the parameters in the .bat file and just run the package from the task scheduler.

Answer 2

I recently had a similar problem and my experience can help you. I called a small application, i.e. EXE from a batch file. I planned to run the batch file from the Windows Task Scheduler. An application accessed SQL data using Windows authentication. I could run the application directly, that is, click on the EXE to start it. I can run the application from a batch file. But if I tried to start the scheduled task, it seemed to start, but did nothing and did not post any errors that I could find. I found that if I changed the application to run using SQL Authentication, it could be launched from the task scheduler.

I suspect there is something in the context of Windows authentication when it is launched from the Task Scheduler that is not recognized by SQL.