SQL with Manoj

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:

select @@VERSION

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?

Step 1:
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.
Like:

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:

Step 2:
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.