Categories
export-to-excel openrowset sql sql-server

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.