MSExcel data not Read or Export to file Sql server 2008

Question

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 19-Feb-14 20:39pm

chetan2020

Updated 19-Feb-14 20:55pm

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

chetan2020 · Answer 1 · 2014-02-19T22:46:00

Solution 2

http://stackoverflow.com/questions/13888082/ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null-returned-m

Posted 19-Feb-14 22:46pm

chetan2020

Updated 19-Feb-14 22:49pm

v2

Comments

King Fisher 20-Feb-14 4:51am

mark as an answer

King Fisher · Answer 2 · 2014-02-19T21:05:00

Solution 1

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

Posted 19-Feb-14 21:05pm

King Fisher

Updated 19-Feb-14 21:49pm

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....

King Fisher · Answer 3 · 2014-02-20T19:49:00

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