When reading Excel file from shared path on a remote server, an "access denied" error disp
When reading Excel file from shared path on a remote server, an "access denied" error disp
(OP)
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
I get an error:
How to solve issue above please?
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
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
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
CODE --> python
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)'; CODE --> server
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 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
CODE --> server
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$]') 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
Talk To Other Members
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
And It's actually not a python topic, but a topic how to set the SQL server so that it's python scripts have access to the other machine.
There are several possibilities you could try:
1) Here https://learn.microsoft.com/en-us/answers/question... you got an advice to set permission for Launchpad service. Have you tried it ?
2) Have you tried the sharing I suggested here https://www.tek-tips.com/viewthread.cfm?qid=181837...
in my post on 10 Aug 22 15:25 ? You need to go first to the server \\192.168.7.9 and allow sharing for everyone on the folder \Import\8. Then you could try what I posted 10 Aug 22 15:25.
3) you wrote above: "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 ... and it read the Excel file successfully."
Then if this works for you, then you can create a temporary table on your MS SQL sever, read into the temprary table the data from the excel file located on remote machine (\\192.168.7.9\Import\10\test\testData.xlsx) and then change your python script so that it reads the data from the database table instead of the excel file.
4) you wrote above "When I execute reading to Excel file on local server, it is working from path D:\ExportExcel\testData.xlsx."
Then using OS command you can try to copy the file from \\192.168.7.9\Import\10\test\testData.xlsx to D:\ExportExcel\testData.xlsx everytime before executing your SQL server + python process.
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
really i get good support from this forum
but i try more but ican't get support for remotly reading excel file
because it give me access permission denied
so i don't know how to solve this issue
last thing i try from same server not from sql using python
and it read data success
but from sql it give me access is denied
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
RE: When reading Excel file from shared path on a remote server, an "access denied" error disp
goal is how to read data and write to shared path remotly from another server on domain
so iw ill start test from local read data on same server (D:\ExportExcel\testData.xlsx)
ressult success
access and read shared path from same server \\192.168.7.7\ExportExcel\testData.xlsx
error access is denied
from same machine i using python pysharm without sql
to run
import pandas as pd
df = pd.read_excel(r"\\192.168.7.7\ExportExcel\testData.xlsx",engine='openpyxl')
print(df)
it run success reading
so How to solve my issue