Linked Server Error

• Balz

  Grasshopper

  Points: 20

  More actions

  December 8, 2017 at 12:48 am

  #325010

  Hi,
  I am trying to export the SQL (Server 2012)  data to Excel file.  I tried the below query

  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
  Database=C:\DataIssue.xlsx','SELECT * FROM [Sheet1$]')
  SELECT emplid,deptid FROM employees
   
  And got this error
  
  Msg 7399, Level 16, State 1, Line 2
  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
  
  Msg 7303, Level 16, State 1, Line 2
  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

  Please provide the solution to resolve it.

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  December 10, 2017 at 4:55 pm

  #1971487

  Balz - Friday, December 8, 2017 12:48 AM

  Hi,
  I am trying to export the SQL (Server 2012)  data to Excel file.  I tried the below query

  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
  Database=C:\DataIssue.xlsx','SELECT * FROM [Sheet1$]')
  SELECT emplid,deptid FROM employees
   
  And got this error
  
  Msg 7399, Level 16, State 1, Line 2
  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
  
  Msg 7303, Level 16, State 1, Line 2
  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

  Please provide the solution to resolve it.

  Thanks.

  On which machine is the "C:\DataIssue.xlsx" file?  The SQL Server or somewhere else?  If it's NOT on the SQL Server itself, then you need to define a share where the file is an use a UNC to connect to the spreadsheet.  If it IS on the server, then I strongly suggest you pick some other directory other than the root of the most important drive on the entire server.  I'm not even sure that SQL Server can see the root of the server's C: Drive without granting it some permissions, which you should not.  Certainly, I would not give individuals privs to the root of the C: Drive.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)