Solved

the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine

Posted on 2014-03-09
9
15,509 Views
Last Modified: 2014-03-10
Trying to import data from an excel spreadsheet to my SQL database

Windows 2008 64 Bit Server
SQL 2008
Windows 7 64 Bit
Office 2010 64 Bit

I have a spreadsheet with 2647 rows that was created by doing a dir  command of my folders on my file system. The File was created as a name.csv file I edited the name.csv file then saved it as name.xlsx file.

When trying to import using MSSMS I get the following error
the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine
0
Comment
Question by:Thomas Grassi
  • 5
  • 4
9 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39916605
Install the 'Microsoft Access Database Engine Redistributable' :

http://www.microsoft.com/en-us/download/details.aspx?id=13255

It facilitates the the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server.

Also see here for some more info :

http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 39916648
Thanks for the fast response

Do I need to install this on both my Windows 7 machine and the Windows 2008 server that runs SQL 2008
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39916658
The Windows 2008 server running SQL 2008
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Author Comment

by:Thomas Grassi
ID: 39918956
Paul

Still no luck
I followed the blog  and ran all the query commands.
At the end I got this
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


Also the drop down list only has excel 2003 and Excel 2007 am I missing something here?

Another thing I installed the link you gave me on both the server and the windows 7 machine.

The server does not have office 2010 installed
Microsoft SQl Server Management Studio is 10.50.2500.0 the same as on my server.

I did the same in the blog on both machines on the server I still got the did not register message when trying to import

What am I missing here?

Really need to import this data

I have the SQL tools installed on my Windows 7 computer also
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39919003
enable Ad Hoc Distributed Queries on your sql server by opening a query window to the master database and executing the following query:

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

http://msdn.microsoft.com/en-us/library/ms187569.aspx
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 39919067
Paul

Thanks for responding so fast.

Ran the above

Still have the same issue.

After making these changes do I need to restart SQL or something

Still getting same error not registered
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 39919193
Please try installing the following as well, some users report that this needs to be installed along with the previous install.
This will need to go on your SQL Server.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

http://www.sqlservercentral.com/blogs/bradleyschacht/2011/12/16/the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine/
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 39919220
Paul

I downloaded this
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

already and it is working.

The import runs everything is successful

When I run a query against the table I show no records even though the report showed 2467 records

The execution was successful

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Success)

- Copying to [dbo].[artists$] (Success)
      * 2647 rows transferred

      Messages
      * Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - artists$" (20)" has started.
       (SQL Server Import and Export Wizard)
      
      * Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion  in "component "Destination - artists$" (20)" has ended.
       (SQL Server Import and Export Wizard)
      

- Post-execute (Success)
      Messages
      * Information 0x4004300b: Data Flow Task 1: "component "Destination - artists$" (20)" wrote 2647 rows.
       (SQL Server Import and Export Wizard)
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 39919424
Adding the last download worked.

I just needed to get familiar with the import wizard.

Thanks for your help
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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.
While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

776 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