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.
- 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
- Install it on your server.
- 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.
- 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
The OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server (null) reported an error
Try changing:
SET @ExcelString = 'Excel 8.0;Database=' + @fn
to:
SET @ExcelString = 'Excel 8.0;Data Source=' + @fn +';'
Not 100% sure on the trailing semi-colon so try it with and without
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
Related Topics
Drop All Tables Whose Names Begin with a Certain String
Return Setof Record (Virtual Table) from Function
In Which Sequence Are Queries and Sub-Queries Executed by the SQL Engine
Split One Column Value into Multiple Column Values
What Is the Equivalent of 'Describe Table' in SQL Server
SQL Query to Get Most Recent Row for Each Instance of a Given Key
Use a Union or a Join - What Is Faster
Continuing a Transaction After Primary Key Violation Error
Aggregating (X,Y) Coordinate Point Clouds in Postgresql
Select Corresponding to Row from the Same Table SQL Server
Update Columns Values with Column of Another Table Based on Condition
What Are the Principles Behind, and Benefits Of, the "Party Model"
How Exactly Does Using or in a MySQL Statement Differ With/Without Parentheses
How to Alter This Computed Column in SQL Server 2008
How to Backup a Remote SQL Server Database to a Local Drive
SQL Server Select Where Any Column Contains 'X'