Click here to Skip to main content
15,563,054 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I work with SQL Server 2019 on server I face issue when I try to read an Excel file from shared path using python 3.10.

SQL Server exists on server 7.7 and files exist on another server on Active Directory domain 7.9.

When I execute reading to Excel file on local server, it is working from path D:\ExportExcel\testData.xlsx.

But when try to read the Excel from a remote server as below

EXECUTE sp_execute_external_script
        @language = N'Python',
        @script = N'import pandas as pd
                    df = pd.read_excel(r"\\192.168.7.9\Import\10\test\testData.xlsx", sheet_name = "Sheet1")
                    print(df)';

I get an error:

Msg 39004, Level 16, State 20, Line 48
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

Msg 39019, Level 16, State 2, Line 48
An external script error occurred:

Error in execution. Check the output for more information.

Traceback (most recent call last):

File "", line 5, in
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\9D383F5D-F77E-444E-9A82-B8839C8801E3\sqlindb_0.py", line 31, in transform
df = pd.read_excel(r"\192.168.7.9\Import\10\test\testData.xlsx", sheet_name = "Sheet1")
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 307, in read_excel
io = ExcelFile(io, engine=engine)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 394, in init

Msg 39019, Level 16, State 2, Line 48
An external script error occurred:

self.book = xlrd.open_workbook(self.io)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\xlrd_init.py", line 111, in open_workbook
with open(filename, "rb") as f:
PermissionError: [Errno 13] Permission denied: '\\192.168.7.9\Import\10\test\testData.xlsx'

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 "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.


How to solve issue above please?

What I have tried:

What I tried:

I try to open shared path on remote server; I can open it and create new file and read and write on same path

I tried to use another tool for reading as openrowset

 select * 
 from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\\192.168.7.9\Import\10\test\testData.xlsx;HDR=YES','select * FROM [Sheet1$]')
and it read the Excel file successfully.

Folder path and file have all permission like network service and owner and administrator and authenticated user and every one and all these have full control over all that .

Please - what could be the issue?

I have been trying for over 3 months to solve issue but can't.

Can anyone please help me?

It reading file and display content
Posted
Updated 12-Oct-22 13:24pm
Comments
Richard MacCutchan 13-Oct-22 3:44am    
You need to find out which account is being used when the SP starts the Python script. The permission denied error is clearly telling you that whichever account it is, it does not have permission to access that file. This is not something that we can fix for you.

1 solution

What account is running the Python scripts? Are these Jobs running on the SQL Server?

On the 192.168.7.9 machine, is there a network share setup holding the files you're trying to read? What's the name of that share? In your code, it seems to be named "Import". NO, this is NOT the name of the folder on your 192.168.7.9 machine drive. The folder name and the share name are usually the same, but can be very different.

Next, is the account running the scripts on the remote server setup to have Read permissions to the share? Does it also have permissions to the folder on the drive the share is exposing?

Is your network setup as in an AD domain and are BOTH of these machines in that domain?
 
Share this answer
 
Comments
ahmed_sa 12-Oct-22 20:30pm    
I appreciate your support
1-What account is running the Python scripts? Are these Jobs running on the SQL Server?
yes running on sql server 2019 with account admin
2-On the 192.168.7.9 machine, is there a network share setup holding the files you're trying to read? What's the name of that share?
yes there are network shared setup exist and i can expolore it and open it manually
3-Is your network setup as in an AD domain and are BOTH of these machines in that domain?
yes
I try it by openrowset and read data from server 7.9 shared path
to server 7.7 by using openrowset
4-is the account running the scripts on the remote server setup to have Read permissions to the share?
yes have permission read
Dave Kreskowiak 12-Oct-22 21:05pm    
1 - There is no such thing as account "admin" unless you created it. What is the name of the account that is running the code?

The reason you're getting "Access Denied" in the Python code running on the SQL Server is because the account running the code, most likely, does NOT have Read/Write permissions to either the Share or the Folder, or both. Those permissions must be setup properly for this to work.

Another possibility is the file your code is trying to open is locked by another process and has exclusive access to the file. The process that has the file open has to close it before another process (your code) can open the file.

ahmed_sa 12-Oct-22 20:36pm    
also it is not job it is python script running on sql server
Dave Kreskowiak 12-Oct-22 21:08pm    
So I take it this means you are running the code either from a CMD Prompt or some environment you launched when logged into the machine?
ahmed_sa 12-Oct-22 21:35pm    
thanks for support
sure file not open
and if account i run python from it not have permission
i use this account with open rowset and read data
so can you give me more detail please

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900