I am using the below code:
I get an error returned:
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
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)".
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