Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 16266
  • 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 JacksonCommented:
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 JacksonCommented:
The Windows 2008 server running SQL 2008
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 JacksonCommented:
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 JacksonCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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