1

I need to build a model using a Python script in SQL Server. But it needs to have multiple input data sets. And I gather you can only have one. I read that a way to do it is to use pyodbc inside sp_execute_external_script.

But it won't connect. The exact same code does work in Spyder however so it's not a general configuration issue (I can also connect from Tableau etc.). As it's the local instance I tried using a dot (.) for the server name but that didn't work so I also tried just putting the server and instance name in full (hence the two different connection strings below).

The code:

EXEC sp_execute_external_script 
@language =N'Python',
@script=N'

import pyodbc

sql_conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=XXX\YYY;DATABASE=MY_PYTHON;trusted_connection=Yes")

sql_conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=.;Database=MY_PYTHON;Trusted_Connection=Yes;")
'

The error message:

Msg 39004, Level 16, State 20, Line 52
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 52
An external script error occurred:

Error in execution. Check the output for more information. Traceback (most recent call last):
File "", line 5, in
File "C:\ProgramData\GTMDEV\Temp-PY\Appcontainer1\A0F0D72C-4B70-46BD-8307-D51C36E9A8F1\sqlindb_0.py", line 39, in transform
sql_conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=.;Database=MY_PYTHON;Trusted_Connection=Yes;")

Msg 39019, Level 16, State 2, Line 52
An external script error occurred:
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2]. (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (2)')

SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL15.GTMDEV\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.GTMDEV\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params) RuntimeError: revoscalepy function failed.

Internet research has led me to create a login for SQLRUserGroupYYY in SQL Server and add it to "Allow log on locally" in Local Security Policy. But that has not changed anything. And I can't find anything else that works.

In case it is relevant the server is on my PC and it's name is MYPCNAME\MYNAMEDINSTANCE. (And the name is correct and it is running (suggested in the error message) because the connection works in Spyder, Tableau etc.).

Any ideas what else I can try?

Thanks.

For reference, this is one of the articles that suggested the method:

https://medium.com/wandering-in-advanced-analytics/passing-multiple-t-sql-queries-to-sp-execute-external-script-and-loop-back-requests-d153ee582235

The example given is for R but the principle should be the same for Python. I did try it in R and got the same error message.

This is another post with the same sort of issue (that had a link for the SQLRUserGroup login suggestion):

pyodbc.OperationalError when used inside sp_execute_external_script

free_hat
  • 21
  • 5
  • Why, however, are you using SQL server to connect Python, to connect to SQL server? That sounds pretty counter intuitive, don't you think? – Thom A Jul 07 '21 at 13:35
  • I just want to use a Python script in SQL Server to build a model. But it needs to use multiple tables in a database. I read in several places a way to do that was to use ODBC inside the python script because otherwise you can only have one input data set. The suggested post that answers a similar question does not help because the connection already works in other scenarios. – free_hat Jul 07 '21 at 13:41
  • @free_hat the error says that either the server you try to connect to is wrong or down. The two connection strings point to completely different instances. One is the local default instance the other a remote named instance. Are you sure these are correct? If you use SQL Server Express you need to connect to the named instance `SLQEXPRESS` eg with `.\SQLEXPRESS` or `localhost\SQLEXPRESS` – Panagiotis Kanavos Jul 07 '21 at 13:56
  • Thank you for the helpful response. Both servers are one in the same. I tried the dot (.) for the local instance (which it is) but that didn't work so (out of desperation) I tried the full server\instance. It's definitely correct because I can connect from other tools like Power BI and Excel (I have tried loads). I will try putting \NAMEDINSTACE after the dot as you suggest now. Sorry for being unclear. I'll edit the question to state that. Thanks again. – free_hat Jul 07 '21 at 14:00
  • I'm 99% certain it is some sort of security issue -- other scenarios result in a server does not exist type error even when it does -- because of a lack of access or privileges. That was certainly the case for a recent issue I had with SQL Server Agent. As a workaround for now I used pickle to serialise the data so it could be imported then de-serialised. I haven't put this as the answer because it doesn't fix the pyodbc issue but it does propose an alternative. – free_hat Jul 08 '21 at 08:53

0 Answers0