How to Execute Python Script as Administrator in SQL Server Agent Job

Question

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).

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.

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":

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

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:

Any ideas here?

gbeaven · Accepted Answer · 2021-03-30 20:16:27Z

3

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"

edited Mar 30, 2021 at 20:16

answered Feb 14, 2019 at 15:48

gbeaven

1,8302 gold badges23 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ray's Web Presence Over a year ago

Would you mind elaborating on the credential/proxy account bit and Run as part?

Lucas Sousa Over a year ago

How did you configured the proxy?

mpy Over a year ago

@LucasSousa did you find out the solution?

Alvaro Parra · Accepted Answer · 2019-02-13 22:10:01Z

0

Did you try using sp_execute_external_script ?

execute sp_execute_external_script 
@language = N'Python', 
@script = N'
a = 1
b = 2
c = a/b
d = a*b
print(c, d)
'

For example, this code can be scheduled into SqlAgent.

answered Feb 13, 2019 at 22:10

Alvaro Parra

8052 gold badges9 silver badges25 bronze badges

1 Comment

gbeaven Over a year ago

Yes, I saw sp_execute_external_script but unless there's a way to directly reference a .py file this just seems like a band-aid solution. I would rather not embed 100+ lines of Python into a job. Seems much more intuitive to just call the script that already exists on the server.

Collectives™ on Stack Overflow

How to Execute Python Script as Administrator in SQL Server Agent Job

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related