Excel 2007 openquery

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

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
cb1393Commented:
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
 
cb1393Commented:
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
 
cb1393Commented:
To clarify my comment above, the linked server connection I was referring to was to an Excel 2007 workbook.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Agreed...throwing this over the side.

Look for next question in a moment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.