none
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

    Question

  • Hi Team,

    I am trying to Export data from SQL Sever Table to Exel sheet by using the below query

    INSERT

    INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=D:\New Folder\New Microsoft Office Excel Worksheet.xlsx;','SELECT * FROM [Test$]')SELECT * FROM [TableName]

    the above code is working in my local but when I execute the same in server I am getting the below error message.

    Msg 7302, Level 16, State 1, Line 1

    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

     

    Server Configuration Details are Windows7

    can you help me on how to resolve the above

    Wednesday, October 12, 2011 8:12 AM

All replies

  • This is because the excel file is located on your local machine & its looking on server at the path you've mentioned above.

    Provide shared path by providing your machine name.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, October 12, 2011 8:19 AM
  • Another option is that the driver is not installed on your server or running on 64 bit. I assume the same path and file is available on your server.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Wednesday, October 12, 2011 8:31 AM
  • HI nmitta !

    Try to run and set these setting;

    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    Go
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Wednesday, October 12, 2011 8:41 AM
    Answerer
  • Check this blog post - may be of help

    Dealing with Microsoft.Ace and OPENROWSET Errors


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, October 12, 2011 5:48 PM
    Moderator