Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:enter image description here

Thanks in advance.

share|improve this question
1  
Is there any chance that in fixing this for posting, you've broken the query? You have an unescaped single quote in your extended properties section. –  billinkc Dec 9 '13 at 1:25

1 Answer 1

I would recommend using SSIS. As it has the flexibility to allow you to save the package and reuse it for subsequent runs and it can even be scheduled using sql agent job.

You are also compromising security by allowing ad-hoc distributed queries to run (changing it to 1).

I have tried the exact same query that you posted and it works for me.

sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure with override
go
SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Documents and Settings\kin\Desktop\test_kin.xlsx; Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1'
,'SELECT * FROM [Sheet1$]') AS exl
GO

select * from #myExcelData

enter image description here

share|improve this answer
    
Thanks for your time. I think the reason it's not working for me, is because I don't have 'Microsoft.Ace.OLEDB.12.0' among the providers as you specified in your answer. Could you please advise me how to add it? I've edited my question to include the provider lists I've got currently. –  Sky Dec 9 '13 at 3:06
1  
@NazilaBeikpour you have to download and install from Microsoft Access Database Engine 2010 Redistributable –  Kin Dec 9 '13 at 3:14
    
I installed it from the link above, reopen the SQL management studio but still this provider is not listed there. Is there anything else should be done to add it to the provider list? –  Sky Dec 9 '13 at 5:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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