The Ole Db Provider "Microsoft.Ace.Oledb.12.0" for Linked Server "(Null)"

The OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null)

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue.
For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.

OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null) returned message Bookmark is invalid.

Try this

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 8.0;Database=C:\Export.xls;', 
                       'SELECT id_sale FROM [ExportSheet$]')
SELECT id_sale
FROM dbo.Sale

OR

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Export.xls;Extended Properties=EXCEL 8.0')...[ExportSheet$]
SELECT id_sale
FROM dbo.Sale

Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

I have MS Sql server 2012, and Office 2013. This seems to be very finicky, so you may have to adjust to your particular versions.

1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
2. Install it on your server.
3. Check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it's a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it's a network service account.
4. Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files like this:

Here's the SP_CONFIGURE commands:

SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

On newer SQL Server 2014 You had use 'DynamicParameters' instead of 'DynamicParam'

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Make sure you register msexcl40.dll like this:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

Cannot initialize the data source object of OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null)

This problem resolved itself. There were further updates installed, one of these must have fixed the issue introduced by the previous updates:

Update for Microsoft Office 2010 (KB4011188) 64-Bit Edition
https://support.microsoft.com/kb/4011188

Update for Microsoft Office 2010 (KB2553347) 64-Bit Edition
http://support.microsoft.com/kb/2553347

Security Update for Microsoft Office 2010 (KB2553338) 64-Bit Edition
https://support.microsoft.com/kb/2553338

Thanks for all the help with this!

---

Related Topics