Solved

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

Posted on 2014-03-09
9
15,282 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now