1

I am trying to make something useful with SQL Machine Learning Services and Python.

I am trying to include an external python script into the SQL code. For now I am testing on SQL Server Management Studio on SQL Server 2019 Dev Edition.

Folloing Niels Berglund, excelent article I am doing:

EXEC sp_execute_external_script
@language =N'Python', 
@script=N'
import os
os.system("C:\TEMP\ml.py")
OutputDataSet = InputDataSet
print("Say Hello from SQL Server Management Studio")
',
@input_data_1 =N'SELECT 42' 
WITH RESULT SETS (([TheAnswer] int not null));  
GO

My ml.py script is just this:

#!"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\python.exe"
print("Say Hello from the external script")

When I run my T-SQL script I get this:

STDERR message(s) from external script: 
Access Denied

STDOUT message(s) from external script: 
Say Hello from SQL Management Studio


(1 row affected)

Completion time: 2020-12-23T08:51:59.9264251-05:00

I made sure the script has what I believe are the right permissions:

enter image description here

I even tried just assigning full control to Everyone. I do not see any error on either SQL or Windows Event Viewer.

I have tried with the following variations of os.system() without any success:

os.system("\"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("\"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\python.exe\" \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("python.exe \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

os.system("python.exe C:\TEMP\ml.py")

Any idea is very welcome.

Thank you!

Wilmar
  • 558
  • 1
  • 5
  • 16
  • I think I found the answer I was looking for already in Niel's post (https://nielsberglund.com/2018/03/07/microsoft-sql-server-r-services---sp_execute_external_script---i/) . It would be ```@script = N'source("/path/to/my/script/my_python_script.py")';``` – Wilmar Dec 22 '20 at 20:50
  • What version of SQL server are you using? Here are a couple of links I have saved on this topic. I haven't went down this road yet of using Python on SQL Server, but I am about to. https://learn.microsoft.com/en-us/sql/machine-learning/package-management/install-additional-python-packages-on-sql-server?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/machine-learning/package-management/install-additional-python-packages-on-sql-server?view=sql-server-ver15 – Jason Cook Dec 23 '20 at 00:05
  • Thank you @JasonCook for your comment. I am using SQL Server 2017. I am close to a solution but I am facing a different problem. I am updating the question to reflect it. Thanks! – Wilmar Dec 23 '20 at 13:42

1 Answers1

0

I was finally able to make this work by:

  • Passing python /script location/ to os.system.
  • Placing the script in the cust folder inside python services in my SQL Server instance. I am not overly happy about this. It seems to be a question of folder permissions but I much I tried to mimic them on another more convenient folder, I was not able to make it run successfully.

This is how it looks:

EXEC sp_execute_external_script
@language =N'Python', 
@script=N'
import os
os.system("python \"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\cust\ml.py\"")

OutputDataSet = InputDataSet
print("Say Hello from SQL Server Management Studio")
',
@input_data_1 =N'SELECT 42' 
WITH RESULT SETS (([TheAnswer] int not null));  
GO
Wilmar
  • 558
  • 1
  • 5
  • 16