SQL Error – The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
I was trying to export a SQL table to Excel and I got below error:
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
After searching a bit I came to know that the above provider is not installed, and I need to install the Microsoft Access Database Engine setup, to facilitate 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”.
–> Get the Microsoft Access Database Engine 2010 Redistributable, [link].
There will be two files:
1. AccessDatabaseEngine (for. 32-bit)
2. AccessDatabaseEngine_x64 i.e. 64-bit
If your SQL Server is 32 bit, then install just the first one.
But if its 64 bit, then install both, first 32 bit & then 64 bit.
After a long pause I’m back again to discuss on Power BI.
In my previous Power BI Series first part [link] I discussed about the first component of Power BI, i.e. Power Query and how to use it to discover and gather data.
Power Pivot lets you:
1. Create your own Data Model from various Data Sources, Modeled and Structured to fit your business needs.
2. Refresh from its Original sources as often as you want.
3. Format and filter your Data, create Calculated fields, define Key Performance Indicators (KPIs) to use in PivotTables and create User-Defined hierarchies to use throughout a workbook.
And here in second part I will discuss about few of these features.
–> The benefit of creating Data Model in Power Pivot is that Power Pivot Models run in-memory so that users can analyze 100’s of millions of rows of data with lightning fast performance.
All you need is Microsoft Excel 2013 to create your Data Model. Check this [link] to troubleshoot if you don’t see POWERPIVOT option in Excel ribbon.
–> Creating Data Model:
To create a Data Model you need a Data Source, so we will use SQL Server as a Data Source and I’ve setup AdventureWorksDW2012 Database for our hands-on. Click [here] to download AdventureWorksDW2012 DB from CodePlex.
2. Now on this new window, click on From Database icon and select From SQL Server from the dropdown, this will open a Table Import Wizard Popup window.
–> Now as your Data Model is ready, you can create Pivot Reports in Excel, let’s see how:
2. The control moves to the Excel sheet, select Existing Worksheet on the Popup.
3. Now select following columns form the PivotTable Fields list:
This would give you Total sales across Regions in the Worksheet
4. Let’s add some Slicers to this:
4.a. Click on PIVOTTABLE TOOLS – ANALYZE, here click on Insert Slicer. On ALL tab, select DimDate.FiscalYear column. This will add Year slicer to the report.
4.b. Now again click on the PivotTable Report, you will see the PIVOTTABLE TOOLS on the ribbon bar again. Select Insert Slicer again and select DimProductCategory.EnglighProductCategoryName column.
This way you can add Graphs, Charts and create very impressive Reports UI as per your requirements.
This Power Pivot – Data Model can also be used to create Power View Reports, which we will cover in next part of this series.
In my previous posts I discussed about Power BI [link], what is it, its components, features and capabilities in the new world of Self Service IB.
Self Service BI allows end users to design & deploy their own reports, analyse within an approved & supported architecture and tools portfolios. End users do not have to worry about maintaining databases, doing integrations, creating warehouses/marts, reports, etc. The Self Service BI tool provide features which are capable enough to do all these activities in an automated, quick and efficient way, and all you have to do is learn how to configure these tools.
Power BI works ONLY with Excel & Office 365. It is nothing but a collection of different components which provide features as follows:
1. Power Query
2. Power Pivot
3. Power View (aka Crescent)
4. Power Map (aka GeoFlow)
5. Power Q & A (aka Natural Language Processing)
6. Office 365
7. Windows 8 App
–> Power Query is used to easily discover or gather data from various public or corporate sources, like:
|Web page||SQL Server database||IBM DB2 database||Windows Azure Marketplace|
|Excel or CSV file||Windows Azure SQL Database||MySQL database||Active Directory|
|XML file||HDInsight||SharePoint List|
|Text file||Access database||OData feed||SAP BusinessObjects BI Universe|
|Folder||Oracle database||Hadoop Distributed File System (HDFS)|
–> Let’s see a small demo how you can use Power Query: All you need is Excel and Power Query add-In, which you can download from Microsoft official site, [link].
Below image shows various ways you can access public or corporate data from various sources from an Excel workbook:
1. Online Search
2. From File
3. From Database
4. From Other Sources
Let’s see how Online Search works. The moment you type “Olympics 2014” it populates lot of sources from where you can fetch data from:
On selecting a source it tries to connect to the public portal and displays the Source URL while fetching the data:
And finally populates the data in tabular format in an Excel worksheet:
Go to Ribbon, click “Table Tools” -> Query -> Edit, this will open the “Query Editor” window where you can edit, clean the data. You can even Create Queries that you can save and use again later to refresh your data. Merge different tables in one step; rename, delete or even create fields. Transform your data before even importing it into a spreadsheet.
This way you can get your data ‘analysis ready’ with Power Query !!!
Today while copying data from SQL Server Management Studio (SSMS) into Excel, I observed that all the data is getting copied only to the first column of Excel Sheet. The Data from SSMS in multiple columns was not getting copied to different columns in Excel.
I realized that the previous day I had splitted some comma separated data in Excel to separate columns. So I think Excel saved the same comma-separated setting and thus not allowing to split data in TAB separated format form SSMS.
So I had to do the split of the dataset again, by going to Data ribbon –> Text to Columns option:
Select the Delimiter as TAB and click Next/Finish:
The Dataset in Excel will be separated in different columns just like in the SSMS Results grid:
In my previous post I discussed about querying Excel files with both 2003 & 2007 versions from SQL Server.
Here we will see how can we query Excel 2010 files. Will use the same scripts from my previous post to create Linked Server to point to the Excel source. We don’t need to do any change in the scripts for this version, but we will require some additional steps to do, that we’ll see later on.
Please Note: To use a Linked server for fetching records from a particular source first you have to enable the “Ad Hoc Distributed Queries” configuration setting. This is disabled by default, if not enabled you will see the following error message:
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.
–> So let’s first of all enable this:
USE [MSDB] GO sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OverRide GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE WITH OverRide GO
You can also enable this setting graphically by going to “Surface Area Configuration” and enable it.
–> Now, to access the Excel file there are 2 ways:
1. Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other options
2. Indirectly by creating a Linked Server first of all, then:
2.a. fetching records from Excel by using OPENQUERY() function or
2.b. by using the Linked Server name within the SELECT query
-- 1. Directly, by using OPENROWSET() function SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [Sheet1$]' ); -- OR -- -- 2. Indirectly, by Creating Linked Server & using OPENQUERY: EXEC sp_addLinkedServer @server= N'XLSX_2010', @srvproduct = N'Excel', @provider = N'Microsoft.ACE.OLEDB.12.0', @datasrc = N'E:\SQL Server - Blogs\LinkedServer2010.xlsx', @provstr = N'Excel 12.0; HDR=Yes'; GO -- 2.a. Using OPENQUERY() function: SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]') -- 2.b. Using the Linked Server name within the SELECT query: SELECT * FROM XLSX_2010...[Sheet1$]
Neither of the above options runs and ends up in following error:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “XLSX_2010”.
–> I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider:
USE [master] GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO
The previous error was gone, but this new error surfaced:
Msg 7438, Level 16, State 1, Line 1
The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0” cannot be loaded in-process on a 64-bit SQL Server.
To resolve this error you need to get the “Access Database Engine 2010” to facilitate 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”.
–> Get the Microsoft Access Database Engine 2010 Redistributable [http://www.microsoft.com/en-us/download/details.aspx?id=13255]
There will be two files:
1. AccessDatabaseEngine i.e. 32-bit and
2. AccessDatabaseEngine_x64 i.e. 64-bit
My machine is 64 bit and the SQL Server is also 64 bit, but let’s just check it:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
So, download and install the second “AccessDatabaseEngine_x64.exe” file.
While installing the 64-bit EXE it gives following error:
You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2010, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Microsoft Access Database Engine 2010:
Microsoft Office Professional Plus 2010, Microsoft Office Visio 2010
So, this error tells that the MS Office product installed in my system is 32-bit, that I was not aware of.
To get this resolved you need to have everything in 64-bit environment. So, I will uninstall the 32-bit version of MS Office and re-install MS Office with 64-bit version.
Now if you again run the above queries, you will see them running successfully and pulling records from Excel file.
–> So, what additional steps we did for Excel 2010 to work?
Registered the Microsoft.ACE.OLEDB.12.0 provider for AllowInProcess & DynamicParameters properties.
This option registeres the provider and both the properties in registry. You can check what system stored procedure [sp_MSset_oledb_prop] does with sp_helptext SP.
EXEC sp_helptext 'sp_MSset_oledb_prop'
You can also go and check the registry by typing “regedit” in RUN.
Go to HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> MSSQL10_50.MSSQLSERVER08R2 -> Providers
A new folder entry is made for “Microsoft.ACE.OLEDB.12.0” provider.
And Inside this folder 2 new entries are created for both the properties.
Check the image below:
We installed the “Access Database Engine 2010” drivers to setup a link between MS Office 2010 products and MS SQL Server.
>> Check & Subscribe my [YouTube videos] on SQL Server.