Advertisements
Home > Excel > Querying Excel 2010 from SQL Server in 64-bit environment

Querying Excel 2010 from SQL Server in 64-bit environment


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.
 


Advertisements
Categories: Excel Tags:
  1. September 18, 2016 at 3:36 pm

    Great article. My users provide an xls workbook from Excel 2003. The workbook itself, therefore, is explicitly 32-bit. I have tried all this, and still no luck. The SSMS session that I use is 32-also. I don’t think there is a 64-bit version of SSIS Designers for VS 2013. Consequently, I believe this is a total snafu. The ACE 64 Bit Interface won’t load in SSIS at design time, and the 32-Bit XLS won’t work on the server at Run Time because I installed the 64-Bit interface on the server.

  2. RAJESHKUMAR EDIGA
    September 9, 2016 at 5:10 pm

    soooper yarrrrrrrrr..

  3. James Robb
    February 19, 2014 at 3:48 am

    Hi Manoj,

    I have read through your post – Thank you for the information. I am still having an issue with getting the import to work

    I have installed the Access driver (AccessDatabaseEngine_x64.exe)

    I have configured the script to run the following SP

    sp_configure ‘show advanced options’, 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 1
    GO
    RECONFIGURE WITH OverRide
    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

    So I first create my Temp Table

    The run the SP above then I run the insert into the Temp table defined

    INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)

    SELECT * FROM OPENROWSET( ‘Microsoft.ACE.OLEDB.12.0′,’EXCEL 12.0;Database=C:\Temp\NotInTrbzJan.xlsx;HDR=YES’,’SELECT * FROM [Data$]’)

    GO
    –Disable AdHoc
    sp_configure ‘show advanced options’, 1; RECONFIGURE;
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 0; RECONFIGURE;
    GO

    The error message I get back is

    Msg 7303, Level 16, State 1, Line 4
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

    Can you tell me if or what I have set wrong on the import? Using SSIS at this point is not a real option.

    Thanks in advance.

  4. Rodrigo
  5. October 3, 2013 at 7:24 pm

    Manoj – Thank you so much for clearly explaining this issue. Solved the SQL Server problem I was having with this info.

  6. Tomm
    August 23, 2013 at 11:57 am

    Thank you!

  7. Hemalatha
    August 2, 2012 at 4:19 pm

    i solved the problem. Thank you

  8. Hemalatha
    August 1, 2012 at 5:41 pm

    Hi, i have installed AccessDatabaseEngine_x64.exe on 64bit machine and it worked fine.
    After 120 days it will show the error like ‘Microsoft.ACE.OLEDB.12.0” provider is not registered on the local machine.

    Please kindly help me.

  1. October 23, 2015 at 6:01 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: