使用Windows任务计划程序执行存储过程

时间:2022-01-03 02:23:06

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身份验证的上下文存在一些问题。