Access EXCEL using OPENROWSET

  • I'm trying to access Excel file from SQL Server management studio using OPENROWSET using the below query but getting the error listed below. Any suggestions on this would be appreciated

    Query:

    SELECT *

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

    'Excel 12.0 Xml;HDR=YES;Database=E:\TestFiles\ExcelFile.xlsx',

    'SELECT * FROM [Sheet1$]'

    )

    Error:

    Msg 7415, Level 16, State 1, Line 2

    Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

  • After getting sysadmin rights on the server i'm getting the below 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)".

  • Have a look at this:

    http://msdn.microsoft.com/en-us/library/ms187569(v=sql.105).aspx

    ----------------------------------------------------

  • Hi All,

    This is working for me when I run the query in the remote desktop connection of that server but it is not working when I run it on my local machine when connected to that server in management studio. Any suggestion plz??

  • Hi

    Go to on windows menu go SQL Server management studio, right click, you get list of commands, and click on Run as administrator.

    I guess it should work.

    It just need you're Admin permission

  • Thanks for your reply. I tried but lo luck. Any other suggestions!

    Thanks in advance!

  • shot in the dark

    SELECT * FROM

    OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=E:\TestFiles\ExcelFile.xlsx';

    Extended properties=Excel 12.0', [Sheet1$])

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Have you had a chance to configure distributed ad hoc queries to see if this helps?

    ----------------------------------------------------

  • yes, i've already configured distributed ad hoc queries. Like I said before it is working when I run the query on the SQL server through remote desktop connection of the SQL server but it is not working when I run it on my local machine connected to that SQL server in management studio.

  • have you tried this suggestion

    http://visakhm.blogspot.co.uk/2013/12/how-to-solve-microsoftaceoledb120-error.html

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply