使用 Windows 任务计划程序执行存储过程 [英] Executing a stored procedure using Windows task Scheduler

问题描述

我一直试图在 Windows 任务计划程序中设置一个每小时运行一个存储过程的计划(因为我使用的是 SQL Express 并且无法安装 3rd 方工具)但是在尝试了各种方法之后，例如运行一个来自任务调度程序的 .bat 文件，从任务调度程序打开 SqlCmd 实用程序并传递命令行语法或 .sql 脚本文件我没有运气.

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"

非常感谢

推荐答案

如果您是 sql 实例的管理员(由于您使用的是 SQLExpress，我敢打赌您正在尝试在自己的计算机上执行此操作，因此很有可能您的用户是 sql 实例的管理员)您根本不应该使用 -E，只需忽略它.

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.

从一个简单的 sql 命令开始，如下所示:

Start with a simple sql command like below:

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

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

将 MY_DATABASE 和 MY_TABLE 替换为您的 dbname 和表名.确保您可以从命令行运行它.它应该从您的表中返回数据.(注意命令行选项区分大小写，所以 -s 与 -S 不同)

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)

最后，不要尝试通过任务调度程序提供参数.将带有所有参数的命令放在一个 .bat 文件中，然后从任务调度程序运行批处理.

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.

这篇关于使用 Windows 任务计划程序执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！