Issues with Microsoft.Jet.OLEDB.4.0 provider

Question

Hi,

When I am trying to execute the below query on Windows7 OS and SQL Server2008 Express edition:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Test.xls;','Select * from [Sheet1$]')
SELECT EmpID,EmpName FROM dbo.Emp

i am getting the below error:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Can you help me out how resolev this issue.

 

Answer 1

What Excel version you are using 2003/2007 and is you OS 32 bit or 64?

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 2

check this workaround suggested by pinal in his blog..

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/

Answer 3

Hi Manu,

I am using Excel 2007 and Win7 Enterprise 64 Bit OS

Answer 4

Hi Manu,

I am using Excel 2007 and Win7 Enterprise 64 Bit OS

Did you check the link Ramireddy mentioned?

Also do check this MS link for compatibility issues: https://connect.microsoft.com/SQLServer/feedback/details/581640/trying-to-use-openrowset-to-import-2003-excel-file-into-64-bit-2008-r2-sql-server?wa=wsignin1.0

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 5

Hi,

Microsoft.Jet.OLEDB.4.0 provider is only available in 32-bit operation system. We can't run it in the 64-bit operation system.
As Manu said, we can use Microsoft.ACE.OLEDB.12.0 to connect to Excel 2007. We can download from here.

Thanks,
Ai-Hua Qiu

---

Constant dropping wears away a stone.

Answer 6

A sure way is to use SSIS Import/Export Wizard.

Launch: SSMS --> Object Explorer --> Right Click Database --> Tasks --> Import Data

Wizard video: http://www.youtube.com/watch?v=9Wmdhnx1niU

---

Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM