none
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". RRS feed

  • Question

  • I am trying to execute this Select against my excel file

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=NO; IMEX=1; Database=C:\Temp\test.xlsx', 'SELECT * FROM [Sheet1$]')

    I have followed every single step according to this article here

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

    but still getting the same error as metioned in the title. Can anyone suggest what else could be wrong?

    The environment is SQL 2005 64 bit with Office 64 Bit drivers (Access 64 bit engine installed)

    • Edited by BI Learner Friday, February 28, 2014 6:17 PM
    Friday, February 28, 2014 6:14 PM

Answers

  • I just added Read/Write Rights on the folder C:\Users\<SQL Server Service Account>\AppData\Local\Temp and everything started to work. Please note this is different from what most articles recommend doing which is to give Read/Write rights to folder <installationdrive>:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp folder</installationdrive>

    • Marked as answer by BI Learner Friday, February 28, 2014 8:44 PM
    Friday, February 28, 2014 8:44 PM

All replies

  • Whats your host machine OS?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, February 28, 2014 6:51 PM
  • Can you try below and see if there's any difference?

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=NO; IMEX=1; Database=C:\Temp\test.xlsx', 'SELECT * FROM [Sheet1$]')


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, February 28, 2014 6:55 PM
  • Its Windows Server 2008 R2 Standard 64 Bit
    Friday, February 28, 2014 7:03 PM
  • Doesnt make any difference.

    Here is the full error message

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 4
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Both the Temp folder (C:\Temp) and the Network Service and Local Service Temp folders have full access to both the Service Account as well as the Account I am executing queries under.
    • Edited by BI Learner Friday, February 28, 2014 7:05 PM
    Friday, February 28, 2014 7:03 PM
  • EXEC XP_CMDSHELL 'DIR C:\Temp'
    works fine as well so its not a permissions issue apparently
    Friday, February 28, 2014 7:34 PM
  • I just added Read/Write Rights on the folder C:\Users\<SQL Server Service Account>\AppData\Local\Temp and everything started to work. Please note this is different from what most articles recommend doing which is to give Read/Write rights to folder <installationdrive>:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp folder</installationdrive>

    • Marked as answer by BI Learner Friday, February 28, 2014 8:44 PM
    Friday, February 28, 2014 8:44 PM
  • When I start my SSMS,   I right click on SSMS and Run as Administrator (Win7-64bit machine). Your query runs fine.
    Friday, February 28, 2014 9:16 PM
    Moderator
  • Which is why I believe I need to set those permissions if I don't run it as Administrator.
    Friday, February 28, 2014 9:29 PM
  • Thanks for sharing your solution to solve this problem.
    Friday, February 28, 2014 9:32 PM
    Moderator
  • I am trying to execute this Select against my excel file

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=NO; IMEX=1; Database=C:\Temp\test.xlsx', 'SELECT * FROM [Sheet1$]')

    I have followed every single step according to this article here

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

    but still getting the same error as metioned in the title. Can anyone suggest what else could be wrong?

    The environment is SQL 2005 64 bit with Office 64 Bit drivers (Access 64 bit engine installed)

    Hi ,

    I think this help u little to run excel file in ssis.In instal  Access bit not work well on ssis any version.you need to change debugging 1 option from  true to false.

    This is the step for change it:

    1. Right click on your project then go to properties.

    2.then go to debugging.

    3.Then there is a option debug.go there and change "Run64bitRuntime"  true to false.

    I think your problem solve.

    Told me if u not understand anything my English so bad

    Thanks

    Muzahid.


    Monday, March 3, 2014 11:07 AM
  • Excelente!!1 diste en el blanco

    Julian Castiblanco P. Bogotá, Colombia

    Friday, March 21, 2014 4:25 AM
  • Thanks ! Saved me from writing more query lines by using bcp. Thanks for the solution. 
    Wednesday, May 3, 2017 9:11 AM
  • This was the right answer  for me
    Thursday, February 15, 2018 8:03 PM
  • AND MAKE SURE THE FILE IS CLOSED IT WORKED AFTER I CLOSED THE EXCEL FILE, UNINTENTIONALLY OPEN!
    Saturday, March 10, 2018 9:06 PM