Sql-server – distributed queries are configured to run in single-threaded apartment mode

openrowsetsql serversql-server-2008-r2

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.

Best Answer

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.

Related Question