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.
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.
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
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
http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/
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$]')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With