The OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for Linked Server “(null)”

Hi guys, I want to read an excel file which was ‘xls’ I had always that issue

in Object explorer>Server Objects>Linked Servers>Providers>Microsoft.ACE.OLEDB.12.0>properties

then you have to enable dynamic parameters and allow in-process

if there is no option inside linked servers you should do that

SQL Server 2019C:\Windows\SysWOW64\SQLServerManager15.msc

SQL Server 2017C:\Windows\SysWOW64\SQLServerManager14.msc

SQL Server 2016C:\Windows\SysWOW64\SQLServerManager13.msc

SQL Server 2014(12.x)C:\Windows\SysWOW64\SQLServerManager12.msc

SQL Server 2012 (11.x)C:\Windows\SysWOW64\SQLServerManager11.msc

you can paste last to run window

windows + r then paste which version of sql you have like SQLServerManager15.msc

if you are getting a transport level error when executing openrowset you should update the access database engine

https://www.microsoft.com/en-us/download/confirmation.aspx?id=54920

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0;Database=C:\adress\products.xlsx;HDR=no',
'SELECT * FROM [sheetname$]')
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\adress\products.xlsx;', sheetname$);
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\adress\products.xlsx;Extended Properties="Excel 12.0;IMEX=1;HDR=YES;"')...[sheetname$];