none
Error with OPENDATASOURCE()

    Question

  • Hi, I'm executing the following T-SQL on a SQL 2005 server and get an error message. 

    T- SQL :


    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source =C:\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]


    Error Message :

    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.
    Msg 7301, Level 16, State 2, Line 1
    Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


    However when I run it on some other SQL Server2005 installed on some other machine, It runs successfully and returns the data.
    I checked on the machine it is failing that the provider "Microsoft.ACE.OLEDB.12.0"  is available. Is there something else that is needed and is missing? Please help

    Thanks!
    Friday, February 20, 2009 11:31 AM

Answers

  • Hi,

    Do you connect to the SQL Server using Windows Authentication or SQL Server Authentication?  If you are using SQL Server Authentication, you need to mark sure that the service account has the appropriate rights on the file. Otherwise, the query will have no access to the file. If you are using Windows Authentication, you need to mark sure the Windows account that you use to login the machine has the appropriate rights for the file.



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, February 23, 2009 6:41 AM

All replies

  • Hello,

    Have you verified that (on the machine it fails on) that the mssql service account has the required access to the file and that it's not in use?

    Cheers,
    Rob

    Friday, February 20, 2009 11:51 AM
  • Hi Rob,

    I have verified on the machine where it fails that the mssql service account has required access. I even added the service account as built in administrators (to test) on the box but it still fails.
    On another note, I logged in as the service account in the box where SQL server is installed and then tried to execute the T-SQL and this time it succeed without errors.

    Thanks!!
    Friday, February 20, 2009 12:47 PM
  • Hi,

    Do you connect to the SQL Server using Windows Authentication or SQL Server Authentication?  If you are using SQL Server Authentication, you need to mark sure that the service account has the appropriate rights on the file. Otherwise, the query will have no access to the file. If you are using Windows Authentication, you need to mark sure the Windows account that you use to login the machine has the appropriate rights for the file.



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, February 23, 2009 6:41 AM
  • as all said, It looks like permission issue
    Rajesh Kasturi SQL Server DBA
    Monday, February 23, 2009 10:41 AM