3

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

enter image description here

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:

enter image description here

Any ideas here?

Community
  • 1
  • 1
gbeaven
  • 1,522
  • 2
  • 20
  • 40

2 Answers2

2

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"

gbeaven
  • 1,522
  • 2
  • 20
  • 40
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.

Alvaro Parra
  • 796
  • 2
  • 8
  • 23
  • 1
    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. – gbeaven Feb 13 '19 at 22:43