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?
My environments:
SQL Server 2008 R2, Excel Professional Plus 10 (32 bits)
Also the providers I have in Linked sever as as following:
Thanks in advance.
Best Answer
after installation Restart agent and server services
Enable OLEDB Driver in SQL Server