The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered error with Excel file linked server – AccessDatabaseEngine_x64.exe

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

GO

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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s