16

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.

The following command is in the batch file...

sqlcmd -E -i"C:\Users\Administrator\Desktop\test.sql" -o"C:\Users\Administrator\Desktop\dump.txt"

Thanks a lot

Paul
  • 4,160
  • 3
  • 30
  • 56
richardterris
  • 303
  • 2
  • 4
  • 13
  • 1
    Using sqlcmd looks plausable. Why have you failed on that one ? – e-mre Jan 14 '13 at 15:10
  • Looks helpful: http://stackoverflow.com/questions/3794897/need-help-to-write-bat-file-that-execute-sql-scripts-in-sql-server-2008-and-ano – sgeddes Jan 14 '13 at 15:19
  • 1
    What about the .bat file to run SQLCMD isn't working? Give us details. – Mark Jan 14 '13 at 15:21
  • Not sure - at the moment the action opens the SqlCmd utility "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" and in the arguments field I have sqlcmd -E -i"C:\Users\Administrator\Desktop\test.sql" -o"C:\Users\Administrator\Desktop\dump.txt" – richardterris Jan 14 '13 at 15:23
  • As far as I understand having -E means trusted "connection" and not defining the server (-S) means it will choose the default instance. In the sql script file I just have EXEC databasename.dbo.storedprocedurename which works when I enter it in the sqlcmd utility manually but when I click "run" on the task nothing happens - not sure if it's doing anything or not but there's nothing appearing in the table and the dump.txt file I'm specifying as output isn't being created so my guess is it's doing nothing – richardterris Jan 14 '13 at 15:25
  • Under which **account** does your scheduled task run? Does that account even have access to the SQL Server instance in question? – marc_s Jan 14 '13 at 15:56

2 Answers2

17

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.

e-mre
  • 3,305
  • 3
  • 30
  • 46
  • No I've remotely connected to the machine where SQL server is, not trying to do this locally. I'll give your suggestion a try, but regarding the batch file - how would that file look? – richardterris Jan 14 '13 at 15:38
  • 1
    SQLExpress is configured to deny remote connections by default. Are you sure you can connect to that remote SQLExpress from your machine? You can try with Management Studio for example – e-mre Jan 14 '13 at 15:39
  • Yes I can connect to it from management studio on my own machine – richardterris Jan 14 '13 at 15:40
  • 2
    so I still recommend ignoring the -E parameter and to specify a server with -S, specify username and password with -U and -P respectively. First forget scheduling and make sure you can run the command from command line. – e-mre Jan 14 '13 at 15:43
  • When connecting to remote SQLExpress from your machine, are you using SQL authentication or windows authentication? – e-mre Jan 14 '13 at 15:44
  • I can run the command from SqlCmd utility - my stored procedure executes fine, so it's definitely related to how I've set the task up.. My action opens SqlCmd utility. What do you recommend I do from there? – richardterris Jan 14 '13 at 15:44
  • 1
    let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22707/discussion-between-e-mre-and-richardterris) – e-mre Jan 14 '13 at 15:47
0

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.

davidb
  • 21
  • 1