I am testing some scenarios with SQL Server Machine learning. I was trying to query an external Web Service from inside a Python script in SQL Server Management Studio. Something like this:
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
The ml.py script looks like (simplified):
#!"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\python.exe"
import pandas as pd
import urllib3
http = urllib3.PoolManager()
response = http.request('GET', 'https://example.com/myWS.json')
df = pd.read_csv(response.data) # Web Service returns a plain csv like payload.
# Keep doing something interesting
When I run ml.py on the console, it runs just fine. However, when I try to run from SQL Server Management Studio I get the following error:
Msg 39019, Level 16, State 2, Line 1
An external script error occurred:
SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
There was an error getting the leads fields: HTTPSConnectionPool(host='example.com', port=443): Max retries exceeded with url: /myWS.json (Caused by NewConnectionError('<urllib3.connection.VerifiedHTTPSConnection object at 0x000002076ECACC18>: Failed to establish a new connection: [WinError 10013] An attempt to access a socket in a way forbidden by its access permissions was attempted'))
So, it is clear there are some communications permissions to set but I am not sure where I am supposed to set them.
My objective is to insert the results of the Web Service directly into a SQL query without having to import from an external file. I am not sure this is the best approach but I just wanted to test to see its viability.
Any idea is welcome.
Thank you!
