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.

  • 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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.