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.
我一直在尝试设置一个计划，以便在Windows任务计划程序中每小时运行一次存储过程（因为我使用的是SQL Express并且无法安装第三方工具）但是在尝试各种方法（例如运行.bat文件）之后从任务调度程序，从任务调度程序打开SqlCmd实用程序并传递命令行语法或.sql脚本文件我没有运气。
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
非常感谢
2 个解决方案
#1
11
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.
如果您是sql实例的管理员（因为您使用的是SQLExpress，我打赌您试图在自己的计算机上执行此操作，因此您的用户很可能是sql实例的管理员）您不应该使用-E at所有，只是忽略它。
Second, specify the server even if you are working on local.
其次，即使您在本地工作，也要指定服务器。
Start with a simple sql command like below:
从一个简单的sql命令开始，如下所示：
sqlcmd.exe -S "." -d MY_DATABASE -Q "SELECT * FROM MY_TABLE"
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)
用您的dbname和表名替换MY_DATABASE和MY_TABLE。确保您可以从命令行运行它。它应该从您的表中返回数据。 （注意命令行选项区分大小写，因此-s与-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.
最后，不要尝试通过任务计划程序提供参数。将包含所有参数的命令放在.bat文件中，然后从任务计划程序运行批处理。
#2
-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.
我最近有类似的问题，我的经验可以帮助你。我正在调用一个小应用程序，即批处理文件中的EXE。我正在安排批处理文件从Windows任务计划程序运行。该应用程序使用Windows身份验证访问SQL数据。我可以直接运行应用程序，即单击EXE运行它。我可以从批处理文件中运行应用程序。但是，如果我试图运行计划任务，它似乎开始但没有做任何事情并且没有发现我能找到的错误。我发现如果我将应用程序更改为使用SQL身份验证运行，则可以从任务计划程序运行。
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.
我怀疑当从SQL程序无法识别的任务计划程序运行时，Windows身份验证的上下文存在一些问题。
#1
11
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.
如果您是sql实例的管理员（因为您使用的是SQLExpress，我打赌您试图在自己的计算机上执行此操作，因此您的用户很可能是sql实例的管理员）您不应该使用-E at所有，只是忽略它。
Second, specify the server even if you are working on local.
其次，即使您在本地工作，也要指定服务器。
Start with a simple sql command like below:
从一个简单的sql命令开始，如下所示：
sqlcmd.exe -S "." -d MY_DATABASE -Q "SELECT * FROM MY_TABLE"
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)
用您的dbname和表名替换MY_DATABASE和MY_TABLE。确保您可以从命令行运行它。它应该从您的表中返回数据。 （注意命令行选项区分大小写，因此-s与-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.
最后，不要尝试通过任务计划程序提供参数。将包含所有参数的命令放在.bat文件中，然后从任务计划程序运行批处理。
#2
-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.
我最近有类似的问题，我的经验可以帮助你。我正在调用一个小应用程序，即批处理文件中的EXE。我正在安排批处理文件从Windows任务计划程序运行。该应用程序使用Windows身份验证访问SQL数据。我可以直接运行应用程序，即单击EXE运行它。我可以从批处理文件中运行应用程序。但是，如果我试图运行计划任务，它似乎开始但没有做任何事情并且没有发现我能找到的错误。我发现如果我将应用程序更改为使用SQL身份验证运行，则可以从任务计划程序运行。
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.
我怀疑当从SQL程序无法识别的任务计划程序运行时，Windows身份验证的上下文存在一些问题。