[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 16373
  • Last Modified:

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

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
Thomas Grassi
Asked:
Thomas Grassi
  • 5
  • 4
1 Solution
 
Paul JacksonSoftware EngineerCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Paul JacksonSoftware EngineerCommented:
The Windows 2008 server running SQL 2008
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
Adding the last download worked.

I just needed to get familiar with the import wizard.

Thanks for your help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now