Click here to Skip to main content
14,770,691 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
See more:
I cant Read data from Excel file or cant to Write in file

My OS system is 64bit
Office 2007
Sql Server 2008

my Query is

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=D:\New folder\book2.xlsx','SELECT * FROM [sheet1$]')

I getting Error like

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

if i run this query i got above error
-------------------------------------------------
second query
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.12.0','Excel 12.0;Database=D:\New folder\book2.xlsx','select * from [sheet1$]')

I getting Error like
The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered.

if i run this query i got above error


Please help, I Already 3 days spent behind it.....
Posted
Updated 19-Feb-14 21:55pm
v3

   
v2
Comments
King Fisher 20-Feb-14 4:51am
   
mark as an answer
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT *, GETDATE() FROM dbo.Products
GO





Updated:
follow this steps
   
v2
Comments
chetan2020 20-Feb-14 3:11am
   
i got this error

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.
King Fisher 20-Feb-14 3:31am
   
run this first
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
chetan2020 20-Feb-14 3:32am
   
yes i follow your step but still above error
King Fisher 20-Feb-14 3:37am
   
64 bit system?
chetan2020 20-Feb-14 3:42am
   
yes
64 bit win-7 OS
office 2007
sql server 2008
King Fisher 20-Feb-14 3:49am
   
updated
chetan2020 20-Feb-14 3:52am
   
means
King Fisher 20-Feb-14 3:58am
   
i said ,check the Solution
King Fisher 20-Feb-14 4:22am
   
got the solution?
chetan2020 20-Feb-14 4:45am
   
i got solution....
first Step:
Create a excel File D:\testing.xlsx

second step:
make sure your selected Columnname presense in excel sheet(latname)

third step :
Run this Query in sqlserver

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 8.0;Database=D:\testing.xlsx;',
                       'SELECT lastname FROM [Sheet1$]')
SELECT lastname
FROM dbo.Employees
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900