Executing a stored procedure using Windows task Sc

Question

I've been trying to set up a schedule to run a stored procedure every hour in Windows Task Scheduler (as I'm using SQL Express and can't install 3rd party tools) but after trying various methods such as running a .bat file from task scheduler, opening SqlCmd utility from task scheduler and passing either the command line syntax or a .sql script file I'm having no luck.

I know this can be done and therefore I'm sure it's something I've missed but if anyone can share their experience of this I'd very much appreciate it.

Thanks a lot

Answer 1

I have recently had a similar issue and my experience may assist you. I was calling a small app i.e. EXE from a batch file. I was scheduling the batch file to run from the Windows Task Scheduler. The app was accessing the SQL data using Windows Authentication. I could run the app directly i.e. click on the EXE to run it. I could run the app from the batch file. But if I tried to run the scheduled task it seemed to start but did nothing and posted no errors that I could find. I found if I changed the app to run with SQL Authentication it could be run from the Task Scheduler.

I suspect there is something about the context of the Windows Authentication when it is run from Task Scheduler that is not recognised by SQL.

Answer 2

If you are an admin on the sql instance (Since you are using SQLExpress I bet you are trying to do this on your own computer so there is a high chance your user is an admin of the sql instance) you should not use -E at all, just ignore it.

Second, specify the server even if you are working on local.

Start with a simple sql command like below:

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

Replace MY_DATABASE and MY_TABLE with your dbname and table name. Make sure you can run it from command line. It should return the data from your table. (Beware command line options are case-sensitive so -s is not same as -S)

Last, do not try to feed parameters through task scheduler. Put the command with all parameters in a .bat file and just run the batch from task scheduler.