1

I'm trying to run an OPENROWSET query to an Excel sheet like so:

SELECT id, SourceName  
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0; HDR=YES; IMEX=1; Database=D:\MyFolder\Configuration.xlsx', 'SELECT * FROM [Sheet1$]')

When I execute this from SSMS it works. As part of a job step, it doesn't work with the error stated.

Things I've tried:

  1. sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO

  2. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO

  3. Setting the permissions on the target spreadsheet & folders to 'Everyone' and 'Read/Write'

  4. When I go to 'Server Objects' -> 'Linked Servers' -> 'Providers', Microsoft.ACE.OLEDB.12.0 is listed and it's in xp_enum_oledb_providers.

  5. Access Database Engine x64 is installed.

This is SQL Server 2014.

Can anyone help? Why would the SQL Server Agent be unable to use the ACE provider when my user can?

Johnny Willemsen
  • 2,942
  • 1
  • 14
  • 16
user1186559
  • 31
  • 6
  • Check the permissions on the %TEMP% folder used by the account running the SQL Server process. The account running SQL Agent may not have permission to write there. See "Tweak #2" in [this answer](http://stackoverflow.com/a/26829350/2144390) for details. – Gord Thompson Dec 01 '16 at 14:20
  • 1
    Thank you Gord! It still didn't work after this, but the service account involvement made me think. I gave read/write permissions to the C:\Users\SQLEngine user (had already given them to the SQLAgent user) and it worked. Without your suggestion I wouldn't have thought to try this, so it's greatly appreciated! – user1186559 Dec 01 '16 at 16:03

0 Answers0