sql 2022, python, and Revoscalepy: DLL load failure on pylink and px_call.

Question

I appreciate any suggestions on this. After following this guide (for Python only): https://tomaztsql.wordpress.com/2022/12/05/using-r-and-python-in-microsoft-sql-server-2022/ I get the error message below whenever I try to execute a python script in Sql2022, such as this simple script: EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python';

The error message is this: Msg 39012, Level 16, State 14, Line 33 Unable to communicate with the runtime for 'Python' script for request id: 5AD43A30-AC40-48D5-BF24-C06EB3CFBEBC. Please check the requirements of 'Python' runtime. STDERR message(s) from external script: Traceback (most recent call last): File "<string>", line 1, in <module> File "C:\Users\username\AppData\Local\Programs\Python\Python311\Lib\site-packages\revoscalepy_init_.py", line 121, in <module> from .RxSerializable import RxMissingValues File "C:\Users\username\AppData\Local\Programs\Python\Python311\Lib\site-packages\revoscalepy\RxSerializable.py", line 27, in <module> from . rxLibs.pylink import px_call ImportError: DLL load failed while importing pylink: The specified module could not be found.

Answer 1

Hi @Manuel Lopez See Install a Python custom runtime for SQL Server

Best regards, Li Hong

Answer 2

Thanks, that wasn't the issue (--I don't believe those older instructions apply to SQL2022). It turns out that revoscalepy wheel works only with Python 3.10. Somehow the guide I linked to has it working with Python 3.11, but I have no idea how. (I put pylink.pyd in the dependency walker and it looks specifically for python310.dll.)

So I have sql2022 integrating with python 3.10, but I don't see how to get it to work with python 3.11--and I don't see a revoscalepy wheel for python 3.11 either. Is there a way to get it to work with python 3.11?

Answer 3

NOTE: The original blog post that I mentioned in the original post has since been updated to reflect that the instructions there do not work with python 3.11, only 3.10.

p.s. The other, custom python route that Li Hong mentioned is a bit involved (requiring cmake and boost python, among other steps) and Microsoft has not updated its extensibility template files in a while, so I wouldn't recommend or trust the custom process in production use. However I did finally arrive at a python 3.11 MSSQL extensibility zip file (by deviating from all these directions at certain more or less obvious points). It's really something Microsoft should be releasing instead, and presumably they will eventually get around to it. But if anyone wants the gory details, these posts cover it:

https://github.com/microsoft/sql-server-language-extensions/tree/main/language-extensions/python

https://nielsberglund.com/post/2021-01-05-write-a-python-39-language-extension-for-sql-server-machine-learning-services/

https://nielsberglund.com/post/2020-12-29-bring-your-own-r--python-runtimes-to-sql-server-extensibility-framework/

https://nielsberglund.com/post/2021-01-03-build-boostpython--numpy-in-windows/

Answer 4

For those using the MS extensibility templates to generate a custom language file, I've attached the vs2022 compiler warnings I received on the final step for python 3.11 (I believe all are loss of data warnings on type conversions): _CompileWarningsAfterBuild.txt

p.s. MS says should work with python 3.11 (--the warnings are benign). I have as of yet tested only with python 3.10:

[https://github.com/microsoft/sql-server-language-extensions/issues/37#issuecomment-1404364056