Answered by:
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.Empi 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.
Tuesday, December 7, 2010 5:31 AM
Answers
-
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- Proposed as answer by Ai-hua Qiu Thursday, December 9, 2010 7:55 AM
- Marked as answer by Ai-hua Qiu Tuesday, December 14, 2010 8:45 AM
Tuesday, December 7, 2010 6:17 AM -
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.- Marked as answer by Ai-hua Qiu Tuesday, December 14, 2010 8:45 AM
Thursday, December 9, 2010 7:56 AM
All replies
-
What Excel version you are using 2003/2007 and is you OS 32 bit or 64?
~Manu
http://sqlwithmanoj.wordpress.comTuesday, December 7, 2010 5:37 AM -
check this workaround suggested by pinal in his blog..
- Proposed as answer by Naomi N Tuesday, December 7, 2010 3:58 PM
Tuesday, December 7, 2010 5:38 AM -
Hi Manu,
I am using Excel 2007 and Win7 Enterprise 64 Bit OS
Tuesday, December 7, 2010 5:45 AM -
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- Proposed as answer by Ai-hua Qiu Thursday, December 9, 2010 7:55 AM
- Marked as answer by Ai-hua Qiu Tuesday, December 14, 2010 8:45 AM
Tuesday, December 7, 2010 6:17 AM -
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.- Marked as answer by Ai-hua Qiu Tuesday, December 14, 2010 8:45 AM
Thursday, December 9, 2010 7:56 AM -
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 SLAMWednesday, May 11, 2011 3:34 PM