sqlmlutils fail to install packages on SQL Server 2022

Got python to work, but installing packages throws this error.

As a FYI - I could not get sqlmlutils to work on 2019. It would look like it install, but the python package would fail to load properly. Pip Install worked on 2019.

Given the location of Python is different on 2022 to 2019, is it possible to use Pip Install on 2022??

sqlmlutils CALL

import sqlmlutils
connection = sqlmlutils.ConnectionInfo(server="xx\xx", uid="sa", pwd="xxx")
sqlmlutils.SQLPackageManager(connection).install("QuantLib")

FULL ERROR

ProgrammingError Traceback (most recent call last)
File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\sqlqueryexecutor.py:67, in SQLQueryExecutor.execute_query(self, query, params, out_file)
66 if params is not None:
---> 67 self._cursor.execute(query, params)
68 else:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. External script request id is CD35FDA4-7E2E-425F-B013-8BD4D069AC74. (39004) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An external script error occurred: \n\r\nError in execution. Check the output for more information.\r (39019)")

During handling of the above exception, another exception occurred:

RuntimeError Traceback (most recent call last)
Cell In [1], line 3
1 import sqlmlutils
2 connection = sqlmlutils.ConnectionInfo(server="xx\xx", uid="sa", pwd="xxx")
----> 3 sqlmlutils.SQLPackageManager(connection).install("QuantLib")

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\packagemanagement\sqlpackagemanager.py:78, in SQLPackageManager.install(self, package, upgrade, version, install_dependencies, scope, out_file)
76 self._install_from_file(package, scope, upgrade, out_file=out_file)
77 else:
---> 78 self._install_from_pypi(package, upgrade, version, install_dependencies, scope, out_file=out_file)

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\packagemanagement\sqlpackagemanager.py:158, in SQLPackageManager._install_from_pypi(self, target_package, upgrade, version, install_dependencies, scope, out_file)
155 target_package = target_package + "==" + version
157 with tempfile.TemporaryDirectory() as temporary_directory:
--> 158 pipdownloader = PipDownloader(self._connection_info, temporary_directory, target_package, language_name = self._language_name)
159 target_package_file = pipdownloader.download_single()
160 self._install_from_file(target_package_file, scope, upgrade, out_file=out_file)

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\packagemanagement\pipdownloader.py:20, in PipDownloader.init(self, connection, downloaddir, targetpackage, language_name)
18 self._targetpackage = targetpackage
19 self._language_name = language_name
---> 20 server_info = SQLPythonExecutor(connection, self._language_name).execute_function_in_sql(servermethods.get_server_info)
21 globals().update(server_info)

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\sqlpythonexecutor.py:56, in SQLPythonExecutor.execute_function_in_sql(self, func, input_data_query, *args, **kwargs)
29 def execute_function_in_sql(self,
30 func: Callable, *args,
31 input_data_query: str = "",
32 **kwargs):
33 """Execute a function in SQL Server.
34
35 :param func: function to execute_function_in_sql. NOTE: This function is shipped to SQL as text.
(...)
54 [0.28366218546322625, 0.28366218546322625]
55 """
---> 56 df, _ = execute_query(SpeesBuilderFromFunction(func,
57 self._language_name,
58 input_data_query,
59 *args,
60 **kwargs),
61 self._connection_info)
63 results, output, error = self._get_results(df)
65 if output is not None:

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\sqlqueryexecutor.py:24, in execute_query(builder, connection, out_file)
22 def execute_query(builder, connection: ConnectionInfo, out_file:str=None):
23 with SQLQueryExecutor(connection=connection) as executor:
---> 24 return executor.execute(builder, out_file=out_file)

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\sqlqueryexecutor.py:41, in SQLQueryExecutor.execute(self, builder, out_file)
40 def execute(self, builder: SQLBuilder, out_file=None):
---> 41 return self.execute_query(builder.base_script, builder.params, out_file=out_file)

File ~\azuredatastudio-python\lib\site-packages\sqlmlutils\sqlqueryexecutor.py:96, in SQLQueryExecutor.execute_query(self, query, params, out_file)
93 continue
95 except Exception as e:
---> 96 raise RuntimeError("Error in SQL Execution: " + str(e))
98 return df, output_params

RuntimeError: Error in SQL Execution: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. External script request id is CD35FDA4-7E2E-425F-B013-8BD4D069AC74. (39004) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An external script error occurred: \n\r\nError in execution. Check the output for more information.\r (39019)")

I found the error log...

Error: 18456, Severity: 14, State: 5.
2023-02-08 17:34:26.98 Logon Login failed for user 'NT Service\SSISScaleOutMaster160'. Reason: Could not find a login matching the name provided. [CLIENT: ]

Why is sqlmlutils looking for this Service Account? Why if it is needed was the service account not created on the install of SQL server 2022.

I can see that 'NT Service\SSISScaleOutMaster160' account exists on the box.

Further update. pip install works.

sqlmlutils has a lot of issues.

sqlmlutils doesn't utilize the service account NT Service\SSISScaleOutMaster160, was this error located in the sql error log? e.g. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG.*
For SQL Server 2019, are you using the Python runtime installed when Machine Learning services was set up? SQL Server does not support manually overwriting the version of Python installed by SQL Server Setup. Microsoft Learn Docs

I was not able to reproduce the error on SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) (Python 3.7.1) after following the steps:

import sqlmlutils
connection = sqlmlutils.ConnectionInfo(server="server", database="database", uid="username", pwd="password")
sqlmlutils.SQLPackageManager(connection).install("QuantLib")

Confirming the package 'quantlib' v1.29 installed using sys.external_libraries_installed view:

select * from sys.external_libraries_installed

or

EXECUTE sp_execute_external_script
  @language = N'Python',
  @script = N'
import pkg_resources
pkg_name = "QuantLib"
try:
    version = pkg_resources.get_distribution(pkg_name).version
    print("Package " + pkg_name + " is version " + version)
except:
    print("Package " + pkg_name + " not found")
'

sqlmlutils uses CREATE EXTERNAL LIBRARY to install packages. Does the account in your connection string have the permissions outlined on that statement's documentation? https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-library-transact-sql?view=sql-server-2017#permissions

For SQL Server 2022, which version of Python and PIP are you using?

Ben WATSON (@BGW1001)

Ben WATSON (@BGW1001), I was able to successfully install the Python package QuantLib in SQL Server 2022. I believe I identified the issue you encountered and I included some background below to help get you get unblocked:

This only occurred when the Python runtime used to run sqlmlutils differed from the Python runtime associated with the SQL Instance. For more information on the requirement that the runtime versions match between SQL server and sqlmlutils, see this Microsoft Doc

A few things to check:

After encountering the failure in SQLMLUtils, login to SQL Server with the same account you use for sqlmlutils to run the following query to return a list of errors SQL may have captured when attempting to install the package

select * from sys.external_library_setup_errors

You may see an error that indicates a platform mismatch.

Error returned from pip, stderr shows: [... , 'ERROR: QuantLib-1.29-cp311-cp311-win_amd64.whl is not a supported wheel on this platform.']

Does the value in your PYTHONPATH system environment variable match that of the Python runtime you expect SQL Server to be utilizing? Having side by side installations of Python on the same machine you have your production SQL server instance may have resulted in environment variable modifications where current values may be different than the ones present when configuring SQL Machine Learning Services and RegisterRext.exe for the first time.

Side-by-side installation with other versions of Python and R is supported, but we don't recommend it. It's supported because the SQL Server instance uses its own copies of the open-source R and Anaconda distributions. We don't recommend it because running code that uses Python and R on a computer outside SQL Server can lead to problems:

Using a different library and different executable files will create results that are inconsistent with what you're running in SQL Server. SQL Server can't manage R and Python scripts that run in external libraries, leading to resource contention.

This was quite some time ago. From memory, it said that it installed, but threw errors when I tried to use quantlib in python.

…

On Tue, 11 Apr 2023, 12:29 pm Sean Leonard, ***@***.***> wrote: @BGW1001 <https://github.com/BGW1001>, I was able to successfully install the Python package QuantLib in SQL Server 2022. I believe I identified the issue you encountered and I included some background below to help get you get unblocked: This only occurred when the Python runtime used to run sqlmlutils differed from the Python runtime associated with the SQL Instance. For more information on the requirement that the runtime versions match between SQL server and sqlmlutils, see this Microsoft Doc <https://learn.microsoft.com/en-us/sql/machine-learning/package-management/install-additional-python-packages-on-sql-server?view=sql-server-ver16#prerequisites> A few things to check: - After encountering the failure in SQLMLUtils, login to SQL Server with the same account you use for sqlmlutils to run the following query to return a list of errors SQL may have captured when attempting to install the package select * from sys.external_library_setup_errors You may see an error that indicates a platform mismatch. Error returned from pip, stderr shows: [... , 'ERROR: QuantLib-1.29-cp311-cp311-win_amd64.whl is not a supported wheel on this platform.'] - Does the value in your PYTHONPATH system environment variable match that of the Python runtime you expect SQL Server to be utilizing? Having side by side installations <https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16#--pre-installation-checklist> of Python on the same machine you have your production SQL server instance may have resulted in environment variable modifications where current values may be different than the ones present when configuring SQL Machine Learning Services and RegisterRext.exe for the first time. Side-by-side installation with other versions of Python and R is supported, but we don't recommend it. It's supported because the SQL Server instance uses its own copies of the open-source R and Anaconda distributions. We don't recommend it because running code that uses Python and R on a computer outside SQL Server can lead to problems: Using a different library and different executable files will create results that are inconsistent with what you're running in SQL Server. SQL Server can't manage R and Python scripts that run in external libraries, leading to resource contention. — Reply to this email directly, view it on GitHub <#108 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXOB25YEG2ZRVR7HSZDOSWTXAS6ZDANCNFSM6AAAAAAUVDEZQI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

Navigation Menu

Repository navigation

sqlmlutils fail to install packages on SQL Server 2022 #108

sqlmlutils CALL

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sqlmlutils fail to install packages on SQL Server 2022 #108

Description

sqlmlutils CALL

Activity

BGW1001 commented on Feb 8, 2023

BGW1001 commented on Feb 8, 2023

BGW1001 commented on Feb 8, 2023

seantleonard commented on Apr 3, 2023

seantleonard commented on Apr 11, 2023

BGW1001 commented on Apr 11, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions