I have an Excel file with data and would like to update a table in database based on the data the Excel file contains.
To do that I want to use
OpenRowSet command. But I get the error below when even I want to have a
SELECT from the Excel data.
SELECT exl.* INTO #myExcelData FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0' ,'Excel 12.0; Database=C:\Dev\ExcelDataImport.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1' ,'SELECT * FROM [Sheet1$]') AS exl GO 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.
As error message says, I think I should configure the
Microsoft.ACE.OLEDB.12.0 to be able to use it for distributed queries like
OpenRowSet. To do that I ran the commond below:
exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
Output message from the command above:
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
But still I get the same error when try to run the
OpenRowSet query.Could you please give me some hints how to resolve this issue and get the OpenRowSet working?
SQL Server 2008 R2, Excel Professional Plus 10 (32 bits)
Also the providers I have in Linked sever as as following:
Thanks in advance.