CODEWITHSUNDEEP
sql-serverimportssmsopenrowset
Sebastián Soto
Sebastián Soto

Reputation: 1

Openrowset doesn't execute in SQL Server

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.

Upvotes: 0

Views: 1249

Answers (1)

Apple
Apple

Reputation: 1

Please try the following steps:

  1. If the *.xslx file is open in Excel, close it.
  2. Run SSMS as Administrator.
  3. 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
  4. 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.
  5. 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.

Upvotes: 0

Related Questions