none
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Question

  • 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. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I have read alot about this error but none of the sluation work for me.

    Here is my setup:

    Frontend - Windows SBS 2011 - Webserver IIS7

    Backend - Windows SBS 2003 - SQL 2005

    What im trying to accomplish -

    upload excel file and import the data to my sql server.

    Code was taking from the following website EXCEL TO SQL code working till the part it suppose to get the data sent to the sql server im able to load the excel file to the upload folder i have on the IIS and able to pick the sheet number the data is on. when trying to submit i get the above error code.

    I follow all the instractions to solve the issue but none help. the guy that wrote the code say i have a problem with the driver but i did a fresh install on on my IIS server which did not help at all.

    Here is a list of what i did on each server

    Web Server II7-

    • Install fresh copy of SBS 2011 and reconfig the IIS7 to work .net4 for my web application.
    • Microsoft Access database engine 2010 installed.
    • give everyone full permission to the NETWORK Services Temp folder

    SQL Server -

    • Microsoft Access database engine 2010 installed.
    • change the SQL Server service to run on local account
    • make sure to enable AD Hoc
    • Reinstall the Microsoft Access database engine 2010
    • Ran the below sql
    USE [master]
     GO
     
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
     GO
     
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
     GO

    I will appreacite any suggestions you might have. Thank you

    *** I just found something i think - when i log in using windows auth and run the following

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=z:\test.xlsx', 'SELECT * FROM [Sheet3$]');

    I get a the data and no err when i login with my string user i get the err message. so this is a permission issue with sql i wonder what permission i need to give my user to be able to use the OLEDB 12???

    • Edited by EZ-2012 Monday, May 07, 2012 11:33 PM
    Monday, May 07, 2012 11:16 PM

All replies

  • SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=z:\test.xlsx', 'SELECT * FROM [Sheet3$]');

    Hello,

    I guess Z: is a mapped network drive? So it's may mapped in your profile, but not it that of the SQL Server service account.

    Additional the service account needs also access permission for that drive & file. You have to ensure, that the SQL Server can access that file.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, May 08, 2012 4:52 AM
  • hi,

    Thanks for your reply. the Z: drive is just an exmple, when i run the ASPX page it ask the user to load the file into a folder which has R/W permission to everyone. Z: drive has the following permission Everyone - Full Control

    Thanks


    • Edited by EZ-2012 Tuesday, May 08, 2012 2:13 PM
    Tuesday, May 08, 2012 2:08 PM
  • And again the question: Is it a network drive?

    By default the "LocalSystem" account is used as service account for SQL Server and this account don't have any permission to access network resources.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by LearnerSql Tuesday, May 08, 2012 3:28 PM
    Tuesday, May 08, 2012 3:09 PM
  • yes it is a network drive.  But even when giving everyone full permission it still wont work. when i copy the file to the local SQL server both of the account working fine.

    Tuesday, May 08, 2012 7:07 PM
  • Hello Every One

    Hi have the Fix for this problem, really is easy, you only must to install el SP1 y SP2 of SQL Server 2008 R2 and thas off
    Thursday, October 17, 2013 10:37 PM