Click here to Skip to main content
15,906,329 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 20:55pm
v3

 
Share this answer
 
v2
Comments
King Fisher 20-Feb-14 4:51am    
mark as an answer
SQL
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
 
Share this answer
 
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
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

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

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



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