CODEWITHSUNDEEP
sqlsql-serveropenrowset
Bruno
Bruno

Reputation: 21

How to allow all users to run this SQL query?

Some context: I am trying to upload data from Comcast into our database. Comcast sends the data in a macro-enabled workbook with file extension ".xlsm" which is disgusting. To circumvent this, I setup a power automate desktop flow to open the XLSM files and save them as XLSX files. I then run this query under an INSERT on our reporting server to get the data into the DB:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
   'Excel 12.0 Xml;Database=\\COMPANYNAME\company\customer\Comcast\Comcast.xlsx;', Sheet1$);

The only problem is, I'd like this to be automated. So I setup a cloud power automate flow to run the desktop flow and then execute a stored procedure in SQL Server. My flow receives the same error I do on my machine as well as any user outside of the COMPANYNAME\administrator session in SSMS on our reporting server:

Msg 7399, Level 16, State 1, Line 5 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 5 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

It has been very difficult to research this issue as there seem to be many possible causes. I'm no IT wizard but I know enough to be dangerous. Thank you in advance for your time and help and I eagerly await your response!

Upvotes: 0

Views: 100

Answers (0)

Related Questions