select of excel file on file server doesn't work

• petitpere

  SSCrazy

  Points: 2520

  More actions

  November 20, 2013 at 1:34 am

  #286444

  Hi all,

  I seldom ask questions, finding answers searching the forums, but for this one I'm really puzzled and would greatly appreciate the help of some expert.

  Running SQL-Server 2008R2 on a dedicated server and accessing the DBs through SSMS.

  I'm trying to query an Excel file (2007) using the Microsoft.ACE.OLEDB.12.0 provider (updated to the last version). FYI inprocess is allowed and dynamic parameters as well and Ad Hoc Distributed Queries are reconfigured.

  I have no problem querying the file if it's stored on the DB server itself, but if it's stored on a file server, then I'm always getting this annoying 7399 error The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.

  The user running the SQLServer service has access to the folder (checked in Windows and with xp_cmdshell).

  Here's the query I try to run:

  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

  'Excel 12.0;Database=\\server\data\directory\sub_dir\xl_file.xlsx;HDR=YES',

  'SELECT * FROM [Sheet1$]')

  If anyone knows why this all works if the file is stored on the server and doesn't work if it's stored on a file server, please be so kind to help me.

  Thanks a lot

• petitpere

  SSCrazy

  Points: 2520

  More actions

  November 22, 2013 at 3:08 am

  #1668333

  This is really a problem for me and no answer despite 120 views ...:crying:

  Let me re-phrase my question:

  Is it possible to create a linked server to a file (best Excel else CSV or text) which is located on another server i.e.

  FILE_SRV holds the file

  SQL_SRV queries the file on FILE_SRV

  Please tell me if something's unclear ...

  Thanks