sqlmlutils fail to install packages on SQL Server 2022 #108
Comments
|
I found the error log... Error: 18456, Severity: 14, State: 5. 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. |
I was not able to reproduce the error on import sqlmlutils
connection = sqlmlutils.ConnectionInfo(server="server", database="database", uid="username", pwd="password")
sqlmlutils.SQLPackageManager(connection).install("QuantLib")
select * from sys.external_libraries_installedor 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? |
|
@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:
select * from sys.external_library_setup_errorsYou may see an error that indicates a platform mismatch.
|
|
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:
***@***.***>
|
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)")
The text was updated successfully, but these errors were encountered: