'Microsoft.ACE.OLEDB.12.0' 64x Sql Server and 86x Office???

Question

The error: OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

And the answers I'm seeing is a conflict between 64 bit Sql Server and 32 bit Office. Is there a way to run an openrowset on Excel into Sql Server?

insert into dbo.FiscalCalendar 
select * from 
openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\Users\v-chrha\Desktop\fy11.xlsx;',
'Select * from [Sheet1]')

Answer 1

Looks like Microsoft hasn't come up with a solution on this yet.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125117&wa=wsignin1.0

They have some workarounds like using SSIS

Answer 2

You have to install the Microsoft Access Database Engine 2010 Redistributable 64-bit first.

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

Assume that your Excel file is: E:\Sample.xls

Assume that your Excel sheet name is: Sheet1

Run the following to get the data from Excel file:

SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\Sample.xls"; Extended properties=Excel 8.0')...Sheet1$

The above query is running on Windows Server 2008 64-bit with SQL Server 2005 64-bit and SQL Server 2008 R2 64-bit installed.

The key is to specify the 64-bit provider 'Microsoft.ACE.OLEDB.12.0' instead of 32-bit only Jet engine.