On SQL server we can create linked server to Excel files using:
EXEC master.dbo.sp_addlinkedserver @server = N’Abc’, @srvproduct=N’Excel’, @provider=N’Microsoft.ACE.OLEDB.12.0′, @datasrc=N’\\abcfilesrvr\xyz\AbcFilename.xls’, @provstr=N’EXCEL 8.0′
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Abc’,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
On doing test connection we received error: “The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered“.
The solution is to install AccessDatabaseEngine_x64.exe – Microsoft Access Database Engine 2010 Redistributable.
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server.
So thus installing Microsoft Office Data Connectivity Components – AccessDatabaseEngine_x64.exe fixed the issue.
The reason we installed 64 bit exe is because our server was 64 bit and the SQL Server was also 64 bit. We can check by select @@version query on SQL server and server computer properties.
While installing if you get any error related to 32 bit installed exe or any office product, then we need to uninstall it and reinstall the 64 bit one.