Excel X64 driver and SQL Server 2008 R2 on cluster node [Fix]

March 23rd, 2011 by OZ | Posted under Database, SQL Server.

As we continued with the execution of our migration plans from SQL Server 2005 to 2008 R2 X64, we found challenges than needed to be resolved pretty quickly. The latest issue we encountered was the Microsoft.ACE.OLEDB.12.0 provider in SQL Server 2008 R2 X64.

With that same provider, we were able to load Excel files directly into SQL Server 2005 X64 (SP3 and SP4), but had no luck in 2008 R2.

The prerequisites are to install the Microsoft Access Database Engine 2010 Redistributable, and to add the provider, with a couple of configuration changes:

Configuring:

1
2
3
4
5
6
7
8
9
10
11
12
13
exec sp_configure 'show advanced options', 1;
reconfigure;
GO;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;
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;

Testing:

1
2
3
4
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=M:\MSSQL10_50.MWTOOLS\MSSQL\DATA\dreambox.xlsx;HDR=Yes;IMEX=1',
'SELECT * FROM [Subsets & Ads$] WHERE Headline IS NOT NULL')

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

We had no luck with the provider until we pinpointed a workaround on a blog post using the MSDASQL provider instead

Workaround:

1
2
SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);FIL=excel 12.0;DriverId=1046;DefaultDir=M:\MSSQL10_50.MWTOOLS\MSSQL\DATA\dreambox.xlsx', 'SELECT * FROM [Subsets & Ads$]')

After the change our application worked flawlessly.

Share

Tags: , , , , ,

  • http://www.expyram.com Expyram

    Running SQL 2008 R2 x64 on Windows Server R2 SP1 x64.  Installed the x64 Ace drivers.  I was unable to create linked servers, or execute OPENROWSET queries, receiving, depending on the approach, the various error messages “Could not find installable ISAM”, “Unspecificied Error”, and others.  I could successfully do all these things from x86 servers; in fact, I could successfully execute OPENROWSET queries on the x64 server when using SSMS remotely from a x86 machine with the x86 Ace drivers.

    My solution was to log into the server once using the service account for SQL Server.  Everything worked immediately thereafter, and I replicated this on a second server.

    I read elsewhere that the Ace and Jet engines both require access to the Temp folders to do their work.  I suspect logging in once was necessary to “wire-up” access to the Temp folder.

    • http://ozamora.com Oscar Zamora

      Thanks for the input. One of my guys was telling me something similar. Seems
      to be the case.

  • Guest

    Excellent!  I’m running 64 Bit SQL 2008 R2 on a Windows 7 laptop and couldn’t get the linked server to connect.  Your configuration script worked a treat.  After so many hours of trolling through rubbish for an answer I’m very glad to have found your solution.

site tracking with Asynchronous Google Analytics plugin for Multisite by WordPress Expert at Web Design Jakarta.