Home Forums SQL Server 2012 SQL 2012 - General Linked Server Error RE: Linked Server Error

  • 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)