OLE DB error when trying to query excel sheet from Management Studio

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  February 22, 2012 at 10:27 am

  #268688

  Hi,

  I have a user who is getting the following error when trying to query an excel file from within SQL Mgmt Studio:

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

  I can successfully run this on the server and we both can run it successfully on our test server. But for some reason he cannot run it on the production server. I have checked all the settings and even given his account sysadmin just to rule out a permission issue but don't know what else I can be missing. This is the query we are both running:

  select *

  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=c:\Bea\test.xlsx;Extended Properties=Excel 12.0')...[test$]

  I have checked the property on the excel file we are accessing to be sure he had rights to it and it looks okay. I even had him create his own file and he still gets the same error. I have read through alot of posts online but they all seem to deal with the issue if it does not work at all...but for us it is only not working for him on this particular server. Any ideas what would allow me to run it okay but not another user who has all the same permissions as I have and we have tested multiple access files?

  Thanks! 😀

  Isabelle

  Thanks!
  Bea Isabelle

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 13, 2012 at 5:18 pm

  #1458935

  Ever figure it out?

  Some random things to check:

  1. Is the Excel file set to read-only on the production server?

  2. Does the SQL Server service account have permission to read the file location?

  3. Does anyone else have the file open in Excel while trying to read the file through SQL Server?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato