installation – Adding Python package to Sql Server 2017 machine learning services breaks it


I am trying to get started running python inside of Sql Server 2017 with Python Machine Learning Services.

Out of the box it works and I can run simple scripts like listing the installed packages:

EXEC sp_execute_external_script 
@language = N'Python',
@script = N'import pkg_resources
print((p.project_name for p in pkg_resources.working_set))'

When I want to add a new package I follow the Microsoft documentation for 2017: https://docs.microsoft.com/en-us/sql/machine-learning/package-management/install-python-packages-standard-tools?view=sql-server-2017
which says to basically just pip install the new package. So I go to the sql server python install:

C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERPYTHON_SERVICES

and run:

scriptspip.exe install pyarrow

This installs an upgraded numpy version as a dependency and when I then try to call even simple python scripts like above I get the following error:

Msg 39012, Level 16, State 1, Line 0
Unable to communicate with the runtime for 'Python' script. Please check the requirements of 'Python' runtime.

Nothing will run while numpy is in this newer version. I can fix the issue by repairing the Sql Server install from the installation media, but that gets me back to my initial setup without the required pyarrow package.

After trying to troubleshoot (incl. installing specific versions of pandas and numpy) I now can’t get back to a working state with repairs – will try uninstall and reinstalling the feature.

I can see that the procedure has changed for installing python packages in Sql Server 2019 (https://docs.microsoft.com/en-us/sql/machine-learning/package-management/install-additional-python-packages-on-sql-server?view=sql-server-ver15) so there might be some flaws in 2017 but I can’t find it documented anywhere.

I run the latest Sql Server 2017 (cu21) developer edition.

Some suspects:

  • I’m not too familiar but it says in multiple places that Sql Server 2017 Machine Learning services comes with Conda and I’m not sure if Conda and pip are compatible?
  • The error suggests a dependency from microsofts revoscalepy on pandas and again from pandas on numpy.