如何在 SQL Server 代理作业中以管理员身份执行 Python 脚本 [英] How to Execute Python Script as Administrator in SQL Server Agent Job

问题描述

我有一个 Python (3.7.2) 文件，它从第三方 API 提取数据，将数据保存在本地 CSVWindows Server 2016 上的文件，然后对 SQL Server 2016 (13.0.4224.16) 的实例执行批量导入.

I have a Python (3.7.2) file that pulls data from a third party API, saves the data in a local CSV file on Windows Server 2016 and then does a bulk import into an instance of SQL Server 2016 (13.0.4224.16).

如果我从提升的命令提示符手动运行脚本，这可以正常工作.但是，我不想手动运行此脚本，因为我希望它每小时执行一次.我知道如何从服务器操作系统上的 Task Scheduler 进行调度，但我想使用 SQL Server Agent 作为自动化作业进行调度.这样我就可以直接使用 SSMS 管理/跟踪作业，而不必登录服务器本身来检查计划任务.

This works without issue if I manually run the script from an elevated command prompt. However, I don't want to have to manually run this script as I'm wanting it to execute every hour. I know how to schedule from the Task Scheduler on the server OS, but I would like to schedule as an automated job using SQL Server Agent. This way I can directly manage/track the jobs with SSMS and not have to log in to the server itself to check the scheduled tasks.

我目前有一个作业配置为 Operating system (CmdExec) 类型的 1 个步骤，并且 Command: 设置为 "C:\Windows\System32\cmd.exe"python C:\PythonScripts\myPython.py":

I currently have a job configured with 1 step of type Operating system (CmdExec) and the Command: set to "C:\Windows\System32\cmd.exe" "python C:\PythonScripts\myPython.py":

我的想法是，cmd.exe 将被打开并使用我提供的 python 路径调用 python.exe 程序.

My thinking with the above is that cmd.exe would be opened and call the python.exe program with the python path I supplied.

但是，当我运行作业时，它只是挂在 In progress 的状态中，并且永远不会完成.手动运行时脚本在 9 秒内成功完成:

However, when I run the job it just hangs in status of In progress and never finishes. The script when run manually successfully completes in 9 seconds:

这里有什么想法吗?

推荐答案

我能够通过添加凭据来解决我的问题/proxy 帐户，将其分配给 Run as 在步骤中，然后将 Command 更改为如下所示:

I was able to solve the problem to my question by adding a credential/proxy account, assigning it to the Run as in the step, and then altering the Command to look like this:

C:\Windows\System32\cmd.exe/C python "C:\PythonScripts\myPython.py"

这篇关于如何在 SQL Server 代理作业中以管理员身份执行 Python 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！