Open BGW1001 opened 1 year ago
BGW1001
commented
1 year ago 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.
BGW1001
commented
1 year ago I can see that 'NT Service\SSISScaleOutMaster160' account exists on the box.
BGW1001
commented
1 year ago Further update. pip install works.
sqlmlutils has a lot of issues.
seantleonard
commented
1 year ago 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.*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")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?
seantleonard
commented
1 year ago @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.
Error returned from pip, stderr shows: [... , 'ERROR: QuantLib-1.29-cp311-cp311-win_amd64.whl is not a supported wheel on this platform.']
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.
BGW1001
commented
1 year ago 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 https://github.com/microsoft/sqlmlutils/issues/108#issuecomment-1502602977, 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_insql. 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)")