36

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.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • This SO link may help you. - http://stackoverflow.com/questions/12090555/get-excel-sheet-into-temp-table-using-a-script – Krishnraj Rana May 02 '16 at 17:18
  • Other thing i note that in your insert statement, instead of `SELECT * FROM dbo.products`, it should be - `INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=G:\Test.xls;', 'SELECT * FROM [Sheet1$]')` – Krishnraj Rana May 02 '16 at 17:21
  • @KrishnrajRana - No difference, Still getting same error :( – Pரதீப் May 02 '16 at 17:32
  • 3
    Maybe a 64/32 bit issue between SQL process and OLEDB driver? – Simon Mourier May 09 '16 at 06:18
  • 4
    Both SQL Server and MS Office (or just OLEDB drivers) must be 64bit version. More info here http://dba.stackexchange.com/questions/60094/sql-server-error-importing-an-excel-sheet – gofr1 May 09 '16 at 13:19
  • "I am doing this in My Home PC and I am the administrator. " - is your SQL Server service runninng under administrator account? – Alex May 10 '16 at 01:20
  • @Alex - How to check whether SQL Server service runninng under administrator account?. Currently am using Windows login to login to SQL Server – Pரதீப் May 10 '16 at 04:32
  • @Prdp - "Start -> All Program -> Microsoft SQL Server xxxx -> SQL Server xxxx Configuration Manager" OR open "Service Control Manager" (Administrative Tools -> Services). Locate "SQL Server" in the list, then right click and select properties, then "Log On" tab. Check the account it is runninn under. You can also change the account there. – Alex May 10 '16 at 06:28
  • 2
    Can you install the ACE drivers instead the Jet drivers and use OPENROWSET('Microsoft.ACE.OLEDB.12.0', – sqlab May 10 '16 at 13:54
  • Enable the Microsoft.Jet.OLEDB options instead of disabling them. I think this may solve your issue – quest4truth May 11 '16 at 19:19
  • SO doesn't want to let me submit answers today for some reason, but just change your 0s to 1s. I'm getting my information from this website: http://sqlsrv4living.blogspot.com/2013_12_01_archive.html – quest4truth May 11 '16 at 19:29
  • @user3481891 - I feel so stupid. After changing to 1 am getting this `The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server. ` – Pரதீப் May 12 '16 at 17:06
  • @user3481891 - As others said it is 32 to 64 bit problem – Pரதீப் May 12 '16 at 17:07
  • Yes, I got that too, but the cause of the original error message is just that the oledb properties were disabled. As is obvious by the long conversation here this isn't a trivial or well documented problem. – quest4truth May 12 '16 at 18:25

4 Answers4

51

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

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
Saber
  • 5,150
  • 4
  • 31
  • 43
quest4truth
  • 1,111
  • 9
  • 14
  • Sorry. I'm going to try to put the rest of this in a comment. I think I've been secured against posting too long a string. – quest4truth May 12 '16 at 22:20
  • 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` – quest4truth May 12 '16 at 22:21
  • Make sure you register msexcl40.dll like this: `regsvr32 C:\Windows\SysWOW64\msexcl40.dll` – quest4truth May 12 '16 at 22:25
  • The basic idea is to then create your linked server and make sure that your excel format matches your query. SO is just not letting me put in the correct syntax. I'll try this on a different computer – quest4truth May 12 '16 at 22:29
  • OK, here's the rest of it. On a different computer so hopefully that will make a difference. Add your linked server like this: `EXEC master.dbo.sp_addlinkedserver @server=N'anyservername', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Path\to\your\excel\file.xlsx', @provstr=N'Excel 12.0, HDR=YES'` – quest4truth May 14 '16 at 20:56
  • Now either of these queries work: Select query: `SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test/x;sx'JDR=YES;IMEX=1', SELECT * FROM [Sheet1$]')` Insert query: `INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test/x;sx'JDR=YES;IMEX=1', SELECT * FROM [Sheet1$]')` SELECT * FROM YourTable – quest4truth May 14 '16 at 20:59
  • 12
    On SQL Server 2014 I had to 'EXEC sp_configure` and use 'DynamicParameters' instead of 'DynamicParam'. – mrówa Mar 14 '17 at 08:12
  • if the file is located on an unc-share, ensure to setup a spn and set permissions on the temp-folder - see https://stackoverflow.com/a/57095126/4049371 – Benjamin Freitag Jul 18 '19 at 13:13
3

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/

Alex
  • 4,885
  • 3
  • 19
  • 39
JimR
  • 41
  • 2
2

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:

  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. Create excel file with respective columns (name and class in this case).

  3. 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$]')
Julio Nobre
  • 4,196
  • 3
  • 46
  • 49
FerroPont
  • 21
  • 2
-3

Please Execute the below queries to fix this problem:

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
Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
shrikrishna das
  • 1