Solved

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

Posted on 2014-03-09
9
15,585 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…

839 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