sql server - Run Python Script from MSSQL

Question

I want to Run or Execute a Python Script from MSSQL 2008.

I have a server with python, and what i want is that any PC in the local network can execute that scripts with some inputs, those inputs are stored in a MSSQL 2008 R2. The python script read those variables with pandas (read_sql) and then do some analysis and it return an image and a table, both of them are saved in a folder of a server and anybody can see it.

The problem I have is: How can I execute a python script that is in another computer that has python, but I dont have it, I just need the Outputs that are stored but those outputs changes with time so i have to execute that script anytime?

I've been trying using MSSQL 20008 with the cmd Job (Operating System (CmdExec)), here is the code:

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'hola' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'hola',  
    @step_name = N'hola1',  
    @subsystem = N'CMDEXEC',  
    @command = N'python ..script.py',   
    @retry_attempts = 1,  
    @retry_interval = 1;
GO  

I execute each part of it but i don't get any error, but when I come to see if the image was created, i cant find it. (I save the image to a known folder: fig.savefig('C:Images3\Prueba3.png') )

I also try using xp_cmdshell

EXEC xp_cmdshell 'python \serverscript.py';  
GO

But it return this error: ""python" no se reconoce como un comando interno o externo", but python is in the path of the system. When I use the cmd and i entre python, it opens python, and when i enter "python serverscript.py" it executes the script

Also with xp_cmdshell i try to use powershell, and make and script executing python, when i execute that script in the power shell it works, but when i enter this code:

EXEC xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file  C:Images3script.ps1';  
GO 

It gives me the same error as in the previous "EXEC xp_cmdshell 'python serverscript.py'".

I'm using anaconda python, and when i enter path in the CMD it is there: See image with the PATH enter image description here

But i don't know how to execute it.

Please can someone help, or know another way to do this.

I appreciate really much your time and your support

Have a really nice day!

Answer 1

It worked using

EXEC xp_cmdshell 'C:/.../python.exe C:...script.py';  
GO

One problem is that you need to use a .exe in the xp_cmdshell, so just with the python command it doesn't work, you must use the python.exe. Another problem was that the python script must be in the computer that is executing the script, previously the script was in a common server, but it denied the permission, and when the script was changed of directory to this server, it worked!.