Openrowset doesn't execute in SQL Server

Question

I am trying to take data from an Excel sheet and store it into SQL Server 2019 through the Openrowset statement. First I asked to enable 'ad hoc distributed queries' and 'show Advanced Options', which I did, but still alleged to execute it gets stuck and not is not providing the desired result. I'm doing this on my local computer.

Example query:

SELECT * 
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=C:\Users\Public\Documents\Prueba.xlsx',
                 'Select * from [Hoja1$]')

AND it is a small Excel file, just a test, 10 rows, no more, I'm doing this on my local computer, I've done it before in my job computer and it works, but not here.

Do you get some kind of an error message? Please Edit your question to include the full and complete error message - as text, not screen shot(s). — AlwaysLearning
– AlwaysLearning, Commented Aug 31, 2023 at 8:32
These queries are executed by SQL Server, and the SQL Server service is running in the context of a particular user (see Services.msc, for example, to see which user that is). Does that service user have read/write access granted to it in the filesystem permissions for the C:\Users\Public\Documents folder? Is the Excel file closed? (Office applications hold non-shareable locks on files they have open.) — AlwaysLearning
– AlwaysLearning, Commented Aug 31, 2023 at 8:34
Also, Microsoft.ACE.OLEDB.12.0 (Microsoft Access Database Engine 2010) is quite old, do you have it installed? Are you not using a more current version, like Microsoft.ACE.OLEDB.16.0 (Microsoft Access Database Engine 2016)? — AlwaysLearning
– AlwaysLearning, Commented Aug 31, 2023 at 8:39
Note that using the ACE OLE provider as a linked server is not a supported scenario (because it's designed for GUI applications) so you won't get any help from Microsoft. It sort-of works, but don't be surprised if you have problems. Instead use a proper import tool such as SSIS. — Charlieface
– Charlieface, Commented Aug 31, 2023 at 13:30

Jeremy Caney · Accepted Answer · 2024-05-31 04:45:02Z

0

Please try the following steps:

If the *.xslx file is open in Excel, close it.
Run SSMS as Administrator.
Give the account used to run the SQL Server (MSSQLSERVER) service full permission on this folder where your .xlsx file is stored. On my machine account: <<Server Name>>\Network Service
Is [Hoja1$] the sheet name or a defined range within the file? Sheet names are formatted as from [Sheet1$]; range names are formatted as from [$Data]. I believe you can use something like from [Sheet1$A2:M26] as well.

I use the following format:

Excel 12.0;IMEX=1;HDR=Yes;Database=F:\\temp\filename.xlsx

Hopefully this helps. Note as well that when the Openrowset fails the file can be locked in an open state. Additional attempts to read the file will fail, until the open lock is removed.

edited May 31, 2024 at 4:45

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered May 30, 2024 at 18:37

Apple

111 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Openrowset doesn't execute in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related