3

I have sql server 2008 R2 64bit Developer's Edition Installed on my machine.

And Microsoft Office 2010 Professional 32bit.

I have been trying to import some Excel data from an Excel sheet into a sql server database. I have used the following query to do this:

Query

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=D:\Files\BlueFile.xlsx;IMEX=1',
                'SELECT * FROM [Sheet1$]')

I understand sql server has disabled this feature by default as a security measure, however I executed the following statements to enable it.

Configuration

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Error Message

Yet every time I execute the select statement to get data from Excel sheet I get the following error.

Msg 7308, Level 16, State 1, Line 1 OLE DB provider
'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

M.Ali
  • 1,800
  • 8
  • 24
  • 36
  • 3
    As far as I am aware, you cannot use the Microsoft.Jet.OLEDB.4.0 driver to import from 32 bit Excel to 64-bit SQL Server. I believe you need the 64-bit Microsoft Access Database Engine 2010 Redistributable. – Mark Sinkinson Jun 11 '14 at 14:54
  • did you check if DTC service is running ? – Anup Shah Jun 11 '14 at 15:25
  • Yes DTC is running I think the problem is what @MarkSinkinson has stated. I have office 32bit and Sql Server 64Bit. I have been doing some search online and a few places does recommend installing 64bit Access Driver to make it work. – M.Ali Jun 11 '14 at 15:29

1 Answers1

2

As Mark has mentioned in the comments, you are running into a bit-level mismatch between driver sets. You will need to either install a 64-bit installation of Office (specifically Excel in this case) or install a 32-bit installation of SQL Server and import the data to that instance.

John Eisbrener
  • 8,970
  • 5
  • 23
  • 55
  • 3
    You don't need the full blown 64 bit Excel. As Mark mentioned, there is a JET redistributable [AccessDatabaseEngine_x64.exe](http://www.microsoft.com/en-us/download/details.aspx?id=13255) – Remus Rusanu Aug 14 '14 at 07:33
  • `You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products` - check my solution here: http://stackoverflow.com/a/43176115/495455 – Jeremy Thompson Apr 07 '17 at 05:41