Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL 2017 and Openrowset query to excel file

Status
Not open for further replies.

timroop

IS-IT--Management
Dec 20, 2004
157
US
I am using the below code:
Code:
select * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'excel 12.0;HDR=yes;imex=1;
driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; 
extended properties=excel 12.0 xml; 
Database=\\sql2016\Imports\SETUP.xls', 'select * from [Employees$]')

I get an error returned:
Code:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
However if I look at the results tab the column headers are returned. They change if I select from a different worksheet in the Excel file so I believe the file itself is accessible and readable.
If there is truly an Access issue how can the system read the file enough to see the headers but not the table data?

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva
 
I'll answer my own question. Thank you for reading if you've gotten this far.

I had not rebooted after loading the Database engine downloaded from:

Have a great day!

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top