Errors when calling Python in SQL 2019 via sp_execute_external_script

Question

Errors when calling Python in SQL 2019 via sp_execute_external_script

Mantini, Ronald M 6

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

1 answer

Your answer

Answer 1

Cris Zhan-MSFT 6,661

Hello @Mantini, Ronald M ,

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

Mantini, Ronald M 6 Reputation points

2021-07-09T16:03:47.387+00:00

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']
Mantini, Ronald M 6 Reputation points

2021-07-09T16:04:18.977+00:00

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?
Cris Zhan-MSFT 6,661 Reputation points

2021-07-12T02:08:36.067+00:00

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
Mantini, Ronald M 6 Reputation points

2021-07-14T01:29:01.113+00:00

thanks again for your follow up..I am out on vacation this week but will try this as soon as I am back. Thanks again-Ron
Ron Mantini 1 Reputation point

2021-07-20T23:19:54.673+00:00

ok I got back to this today..I thought this was one of the problems in the thread you sent me, or I read it somewhere else, that python can't handle the spaces in a directory name. Eg, my SQL 'working directory' is D:\Program Files\Microsoft SQL Server\MSSQL15.SQL01\MSSQL\ExtensibilityData. Although when I installed my 2nd instance on the C drive, it has those same spaces in the directory and works fine, but alas if you install on C you don't have to make the edit to the pythonlauncher.config either..on my working instance installed on C, the working directory is still "WORKING_DIRECTORY=C:\ProgramData\SQL01\Temp-PY".

When I changed my working directory on the problem instance back to D:\Program Files\Microsoft SQL Server\MSSQL15.SQL01\MSSQL\ExtensibilityData., I go back to the original error which is both a 60 second timeout and the corresponding 'bxlserver.exe' errors in the event viewer.

Faulting application name: BxlServer.exe, version: 9.2.0.0, time stamp: 0x5d6469b7
Faulting module name: KERNELBASE.dll, version: 10.0.17763.2028, time stamp: 0x4569c786
Ron Mantini 1 Reputation point

2021-07-20T23:25:20.053+00:00

my assessment at this point is that if you install on any drive outside of C, you probably can't also have spaces in the 'working directory' path, your example has no spaces in the path and I know I read that in previous research.
Cris Zhan-MSFT 6,661 Reputation points

2021-07-21T02:49:15.947+00:00

Hi,

>my assessment at this point is that if you install on any drive outside of C, you probably can't also have spaces in the 'working directory' path

I will take a test on this valuable assessment.
Cris Zhan-MSFT 6,661 Reputation points

2021-07-22T03:58:47.647+00:00

Oh, this time I created a new directory with spaces on the E drive on the original computer and installed a new SQL Server instance. Everything is the default configuration, but there is no problem with executing sp_execute_external_script.
Ron Mantini 1 Reputation point

2021-07-22T15:43:14.217+00:00

that's interesting, although not apples to apples if this is a 2nd instance and the first instance was working correctly. I would have to try this on a server without an existing instance to see if my guess is accurate.

Share via

Errors when calling Python in SQL 2019 via sp_execute_external_script

1 answer

Your answer