Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
Solution 1
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
Solution 2
Check out sp_configure /RECONFIGURE...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
See these links for more info:
https://technet.microsoft.com/en-us/library/aa259616(v=sql.80).aspx
Solution 3
Works !!! Great thanks. Just for 64-bit Win server 2012R2. Let me put the whole working script partially repeating bits from above which are not easy (as for me) to combine together:
-
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
-
Create excel file with respective columns (name and class in this case).
-
Run code below:
sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO -- Until SQL Server 2012 EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 -- SQL Server 2014 or later EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1 EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
-- Now you can export to Excel INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xlsx;', 'SELECT Name, Class FROM [Sheet1$]') SELECT [Name],[Class] FROM Qry_2 GO -- Or import from Excel select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=c:\targetWorkbook.xls;', 'SELECT * FROM [targetSheet$]')
Pரதீப்
My original name is Thanga Pradeep and I am a C#/Sql Server Developer Best answer I ever wrote in SO(based on complexity not on votes) Calculation in Sql Server Some of my answers I enjoyed writing Simplify MS SQL Statements Generate a sequnce number for every 3 rows in SQL Difference between filtering queries in JOIN and WHERE? How to drop all tables from a database with one SQL query? SQL Server Coalesce data set Also 44th to get Sql Server Gold Badge I learned many things from below Sql Server gurus Aaron Bertrand Marc_S Martin Smith gbn
Updated on February 17, 2021Comments
-
Pரதீப் over 3 years
I am trying to export from my
Table
data intoExcel
throughT-SQL
query. After little research I came up with thisINSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=G:\Test.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM dbo.products
When I execute the above query am getting this error
Msg 7302, Level 16, State 1, Line 7 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
So went through internet for solution, got the below link
In the above link they were saying like we need to be administrator to create folder in C drive
TEMP
folder sinceOPENROWSET
creates some files or folder insideTEMP
folderI am doing this in My Home PC and I am the administrator. Still am getting the same error.
SQL SERVER details
Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64) Mar 10 2016 16:49:45 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3 (Build 10586: )
Any pointers to fix the problem will be highly appreciated
Update : Already I have configured the
Ad Hoc Distributed Queries
andExecuted the below queries
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1 GO
now am getting this error
Msg 7438, Level 16, State 1, Line 7 The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server.