Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2007 openquery

Posted on 2011-05-04
4
Medium Priority
?
831 Views
Last Modified: 2012-05-11
I have a file in my C:\temp folder
File name is Test.xlsx

I'm trying to do an openquery on the file...see attached code

I installed the engine and I turned on the Ad Hoc Distributed queries with

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

When I run my attached code I get this error message

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
select * FROM OPENROWSET ( 
  'Microsoft.ACE.OLEDB.12.0' , 
  'Excel 12.0;Database=C:\temp\Test2.xlsx;HDR=YES' , 
  'SELECT * FROM [Sheet1$]' )

Open in new window

0
Comment
Question by:lrbrister
  • 3
4 Comments
 
LVL 9

Expert Comment

by:cb1393
ID: 35690105
According to your query, SQL Server is looking for a linked server named "Microsoft.ACE.OLEDB.12.0". Have you created a link server connection to the Excel file? If so, try using the name of the link instance name without the single quotes.
0
 
LVL 9

Accepted Solution

by:
cb1393 earned 2000 total points
ID: 35690185
BTW- I tried setting up a linked server connection a while back. After turning on Ad Hoc queries, I ran into permissions problems and gave up- I ended up going another direction which worked out well,  and having Ad Hoc queries turned on is less secure anyway.
0
 
LVL 9

Expert Comment

by:cb1393
ID: 35690210
To clarify my comment above, the linked server connection I was referring to was to an Excel 2007 workbook.
0
 

Author Closing Comment

by:lrbrister
ID: 35690218
Agreed...throwing this over the side.

Look for next question in a moment
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question