Pரதீப் Pரதீப் - 2 months ago 32
SQL Question

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

I am trying to export from my

Table
data into
Excel
through
T-SQL
query. After little research I came up with this

INSERT 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

https://blogs.msdn.microsoft.com/spike/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error/

In the above link they were saying like we need to be administrator to create folder in C drive
TEMP
folder since
OPENROWSET
creates some files or folder inside
TEMP
folder

I 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
and

Executed 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.

Answer Source

OK, I made it work for me, and it looks like SO will let me post today.

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

Make sure you register msexcl40.dll like this:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll