SQL Server-Python Permission issue on data export using Python in SQL Server. Msg 39004, Level 16, State 20, Line 11 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004

Question

Getting error on executing below command using SQL Server Management Studio and SQL Server is running on Ubuntu 18.04.

EXEC sp_execute_external_script
@language = N'Python',
@script =
N'import pandas as pd
InputDataSet.to_csv("/home/dataexport/1.csv")',
@input_data_1 = N'select user_id from test.dbo.[users]'
GO

Error:

Msg 39004, Level 16, State 20, Line 11
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 11
An external script error occurred:

Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "/var/opt/mssql-extensibility/data/f131c9cc-88b2-4fb0-9611-311e79fe345b/EC1513EA-1DD0-4174-A820-4652B1F4EF6A/sqlindb_0.py", line 30, in transform
InputDataSet.to_csv("/home/dataexport/1.csv")
File "/opt/mssql/mlservices/runtime/python/lib/python3.7/site-packages/pandas/core/frame.py", line 1745, in to_csv
formatter.save()
File "/opt/mssql/mlservices/runtime/python/lib/python3.7/site-packages/pandas/io/formats/csvs.py", line 156, in save
compression=self.compression)
File "/opt/mssql/mlservices/runtime/python/lib/python3.7/site-packages/pandas/io/common.py", line 400, in _get_handle
f = open(path_or_buf, mode, encoding=encoding)
FileNotFoundError: [Errno 2] No such file or directory: '/home/dataexport/1.csv'

Msg 39019, Level 16, State 2, Line 11
An external script error occurred:
SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "/opt/mssql/mlservices/libraries/PythonServer/revoscalepy/computecontext/RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "/opt/mssql/mlservices/libraries/PythonServer/revoscalepy/RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

I have given full permissions on the SQL Server folders that are existing on /opt/ and /var/opt but no luck. even I gave Sudo permissions to users that are running SQL Server and LaunchPad service but didn't work.

Answer 1

I got the solution. SQL Server Python creates files on its own container. so that will not visible in the local system. now problem is that how to use that file on local machine.

Answer 2

Hi @Jignesh Raiyani ,

According my research, this error also will case by Python script. Please refer to Adding Python Packages to SQL Server 2017, see below:

The error is clear – “unexpected indent”. The error message also points to the exact spot. But you could have many such errors in your script. That’s why using VS Code would be handy, and you can then cut and paste the script into your procedure.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Jignesh Raiyani ,

By default, external Python and R scripts only have read access permission to their working directories. If your Python or R scripts need access to any other directory, you need give either Read & execute and/or Write permissions to the NT Service\MSSQLLaunchpad service user account and ALL APPLICATION PACKAGES on this directory. Please refer to File permissions to get the steps to grant access.

And you could refer to this similar thread: https://learn.microsoft.com/en-us/answers/questions/110773/executing-python-script-from-ssms.html

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.