Solved

Excel 2007 to sql 2005 using openrowset

Posted on 2010-09-16
5
604 Views
Last Modified: 2012-08-14
I've been trying to use Microsoft.ACE.OLEDB.12.0 provider unsucessfully.  I get the message:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

After doing some searching on the web, based on the various recommendations I've reviewed, I've downloaded the driver from:  http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

I still get the above message.  I guess I haven't "registered" the driver yet.  Is that the problem?

Here's my code:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 Xml;HDR=YES;
                   Database=H:\Excel\Test.xlsx',
                'Select * From [Sheet1$]');



0
Comment
Question by:bgernon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Vipul Patel
ID: 33695409
Have you executed below queries?

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

Author Comment

by:bgernon
ID: 33695900
I'm just now remembering that I used to have a local server on my computer at my last job.  I do not have one on this computer. I hesitate to invoke stored procedures from the main server.   Can you tell me if running these will affect my computer only or the main server?  
0
 
LVL 5

Accepted Solution

by:
Vipul Patel earned 500 total points
ID: 33698105
If you are using SQL Server of the main server then it will affect main server.
0
 

Author Comment

by:bgernon
ID: 33702147
Let me make sure I understand what you are saying.  Currently, I am using sql server express from my desktop to query a database on a main server.  If I run the stored procedures from my desktop, it will affect the main SQL server?

I  looked up EXEC sp_Configure.  Based on what I am reading, sounds like the system admistrator needs to turn the Ad hoc so that I can use openrowset and Show advanced options on.  I'll speak with the admistrator.  

Thanks
0
 

Author Closing Comment

by:bgernon
ID: 33702168
Could have used and explaination of what each stored procedure did and who needs to execute them.  I had to look them up on the web.  
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

728 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