question

MantiniRonaldM-9618 avatar image
1 Vote"
MantiniRonaldM-9618 asked RonMantini-9514 commented

Errors when calling Python in SQL 2019 via sp_execute_external_script

Just completed a fresh stand alone install of **SQL 2019 developer edition **where we chose to also install the Python option under Machine Learning Services and Languages. The install was completed successfully.

When calling Python from SSMS using sp_execute_external_script, I get the below error.

Msg 39012, Level 16, State 14, Line 0
Unable to communicate with the runtime for 'Python' script for request id: BFCF876F-40C8-4DD8-B925-2B85DC7FF3F9. Please check the requirements of 'Python' runtime.
At the same time in the event viewer I see these errors around bxlserver.exe

Faulting application name: BxlServer.exe, version: 9.2.0.0, time stamp: 0x5d6469b7Faulting module name: KERNELBASE.dll, version: 10.0.17763.1911, time stamp: 0x206fb2f7Exception code: 0xc06d007eFault offset: 0x0000000000039689Faulting process id: 0x19f8Faulting application start time: 0x01d76d2b6acb7b42Faulting application path: D:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL01\\PYTHON_SERVICES\\lib\\site-packages\\revoscalepy\\rxLibs\\BxlServer.exeFaulting module path: C:\\Windows\\System32\\KERNELBASE.dllReport Id: 5f0d44a2-cef3-4b2f-9584-d704c671cde1Faulting package full name: Faulting package-relative application ID:

A lot of info on the first error is available online, I have tried most with no success yet.

The 'pythonlauncher.log' has this error repeated 7 times every time I try to run the script from ssms:
[2021-07-07 21:33:26.813][000028DC][0000222C][Error] Process::TryTerminate failed with error code: 5

The SQL Server Error Log shows this error:
Message
Failed to retrieve the connection from satellite {CEA94CF6-B944-420F-BF45-5FB8C667761C} after 60000 MilliSeconds. ErrorCode: 0x80040001.

Measures I have already taken include 'enabling' sp_execute_external_script via sp_configure and granting the domain account running the SQL Launchpad Service full access to the 'python services' directory, as well as the administrators group of which our launchpad acct is already a member.
We also added the path to python.exe to the PATH environment variables. I have also applied SQL 2019 CU11 update as well as the latest Python CU.

I found this thread which seems to be the identical problem and the solution was to re-install SQL Server on the C drive. I also installed on a different drive, D for me, I can't imagine this is the only way to make this work (https://social.msdn.microsoft.com/Forums/en-US/197971e9-01e2-45c3-bac1-3a2e2dcaf685/sql-server-2019-unable-to-communicate-with-the-runtime-for-r-script-for-request-id-please?forum=sqldatabasemirroring)

Thanks in advance for any help.
Ron Mantini
Intel Corp

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered RonMantini-9514 commented

Hello @MantiniRonaldM-9618,

Welcome to Microsoft Q&A!

When calling Python from SSMS using sp_execute_external_script, I get the below error:Msg 39012, Level 16, State 14, Line 0


I tried to install SQL Server and machine learning service (Python) on another disk other than the C drive (the default location used by the SQL Server installation), and then I also encountered this problem when I ran any script with sp_execute_external_script in SSMS.

What needs to be mentioned is that the SQL Server instance I installed on the C drive will not be a problem. So reinstalling the SQL Server instance on the C drive indeed is a solution.

After some searching, I found a solution. My SQL Server is installed on the E drive (E:\SQLServer2019),the instance name is lucky.

You need to find and open E:\SQLServer2019\MSSQL15.LUCKY\MSSQL\Binn\pythonlauncher.config, which can be opened with Notepad. If you are prompted to deny access when modify it, you need to provide "full control" permission for the current account that logs in to windows.

Then edit this line:
WORKING_DIRECTORY=E:\ProgramData\LUCKY\Temp-PY

To:
WORKING_DIRECTORY=E:\SQLServer2019\MSSQL15.LUCKY\MSSQL\ExtensibilityData


Read the following blog to get inspired.
https://www.sqlservercentral.com/articles/fixing-mls-python-services-to-load-the-runtime

Best Regards,
Cris

· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks much for your followup Cris. I made the suggested changes and still had no luck.
Earlier I modified 'pythonlauncher.config'-working directory to this

WORKING_DIRECTORY=D:\ExtensibilityData

After restarting everything I got this error in the output

FileNotFoundError: [Errno 2] No usable temporary directory found in ['D:\\ExtensibilityData\\Appcontainer1\\027211C2-713E-4B11-A2D2-49D89C79B5F3\\Packages\\493d6c8de72d213d7346750a09a6245a-appcontainer1\\AC\\Temp', 'D:\\ExtensibilityData\\Appcontainer1\\027211C2-713E-4B11-A2D2-49D89C79B5F3\\Packages\\493d6c8de72d213d7346750a09a6245a-appcontainer1\\AC\\Temp', 'D:\\ExtensibilityData\\Appcontainer1\\027211C2-713E-4B11-A2D2-49D89C79B5F3\\AppData\\Local\\Temp', 'C:\\Windows\\Temp', 'c:\\temp', 'c:\\tmp', '\\temp', '\\tmp', 'D:\\ExtensibilityData\\Appcontainer1\\027211C2-713E-4B11-A2D2-49D89C79B5F3']

0 Votes 0 ·

I decided to skip having this folder on D entirely and followed the advice in the link you provided and moved WORKING_DIRECTORY=C:\temp\ExtensibilityData. I copied all the files, applied permissions for the Launchpad Acct as well as 'SQLRUserGroupSQL01' group that contains the launchpad account, restarted everything, and now I have a different error from SSMS/sp_execute_external_script

SQL Server was unable to communicate with the LaunchPad service for request id: 72FD8008-76D9-48DD-BDF2-35D803DF10B5. Please verify the configuration of the service.

The service is running. I had gotten this earlier and after changing permissions and adding the Lauchpad acct to administrators it went away, but now its back. Any ideas?

0 Votes 0 ·

Hi,

WORKING_DIRECTORY=D:\ExtensibilityData

What happens if you point it directly to the ExtensibilityData folder in the directory where the SQL Server instance is installed, for example: E:\SQLServer2019\MSSQL15.LUCKY\MSSQL\ExtensibilityData.

In my test, I just gave the account that logged in to the windows "full control" permission on the pythonlauncher.config file, and then modified WORKING_DIRECTORY to point it to the ExtensibilityData folder in the directory where the SQL Server instance was installed. all is well.

I don't even need to grant any permissions to the startup account of the LaunchPad service . Everything is the default setting during installation.

Best Regards,
Cris

0 Votes 0 ·
Show more comments