I have been stuck with this for a while but if I get this right - nothing like it. I am trying to run a python script from SQL Server using xp_cmdshell using the below code
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
EXEC xp_cmdshell '<PythonInstallationFolderPath>\python.exe "<.py FilePath>"'
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
The above code works fine - it runs the python script (I can say that it runs because the python script saves some data from me in an excel sheet). In the python script, I have a code to do some formatting on the excel sheet:
import xlwings as xw
# create an excel instance app object as a context manager to make sure files are closed safely
app = xw.App(visible = True)
# open workbook
excel_book = app.books.open(fr'<ExcelFilePath>\Dummy_Name.xlsx')
# fetch active sheet
ws = excel_book.sheets.active
# select Cell A1 and press Ctrl + A
tbl_range = ws.range("A1").expand('table')
# create table
ws.api.ListObjects.Add(1, ws.api.Range(tbl_range.address))
# save and close workbook and app object
excel_book.save()
excel_book.close()
app.quit()
This code runs perfect when I run it from a Python IDE (like anaconda prompt or VSCode). However, when I call the python script from SQL Server using the command xp_cmdshell as shown above, it fails with the below error:
Traceback (most recent call last):
File "", line 80, in <module>
app = xw.App(visible = False)
File "C:\Anaconda\lib\site-packages\xlwings\main.py", line 212, in __init__
self.impl = xlplatform.App(spec=spec, add_book=add_book)
File "C:\Anaconda\lib\site-packages\xlwings\_xlwindows.py", line 296, in __init__
self._xl = COMRetryObjectWrapper(DispatchEx('Excel.Application'))
File "C:\Anaconda\lib\site-packages\win32com\client\__init__.py", line 113, in DispatchEx
dispatch = pythoncom.CoCreateInstanceEx(clsid, None, clsctx, serverInfo, (pythoncom.IID_IDispatch,))[0]
pywintypes.com_error: (-2147024891, 'Access is denied.', None, None)
I can with certainty say that the above error is associated with the xlwings section above. Thats because if I remove the xlwings section, the remaining code in my python script runs correctly.
From the traceback, I can understand that access is denied. Hence, I tried a few ideas like using xw.App(visible = False) instead of xw.App(visible = True) and trying to also run using context manager. But these dint work.
I am new to xlwings and also to shell commands. I found that this error has relation to pywin32 library from other stackoverflow question and answers like these:
Win32com codes not working on IIS
ImportError: No module named win32com.client
I presume xlwings library or xp_cmdshell does some sort of call to pywin32 library to perform the operations above. I also found that this library is an advanced library for conversing with Windows which suggests to me that it will be difficult for me to debug. Hence, I am seeking your expert help. I have been stuck with this for sometime now and would really appreciate a direction to explore towards or maybe help me pinpoint what I am doing wrong.
