none
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

    Question

  • Hi,

    I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');

    The following error is comming:

    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 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Can any one suggest what should I do to resolve?

    Thanks,
    Parmeshthee.


    Learning .Net 3.5
    Thursday, December 18, 2008 10:28 AM

Answers

  • Try run this:

    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

     Then try your query. I have met this issue before and this should works.

    Wednesday, July 22, 2009 7:09 AM
  • Hi

    After reading registry entries for other providers, I have added a new entry to registry and it's working fine now Windows 7 32 bit. It seems that Microsoft does not want to populate ACE OLEDB provider.

    Create a .reg file with text below and replace MSSQL10 (10 = SQL Server 2008) with your MSSQL version and SQLADV with SQL Server instance name. Save the file and run. It'll add this entry to registry and you can use OPENROWSET with ACE OLEDB providers.



    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLADV\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001

    Wednesday, July 15, 2009 1:35 AM

All replies

  • What account was the sql server serivce configured to run under? Does the account has permission to "D:\"?


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, December 19, 2008 5:32 AM
  • Thanks for the quick response, Ricky.

    The sql server service is configured with dwSql account and the machine login account is different. I have given the full permission on D drive to dwSql account.

    But When I execute ( SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]'); using sa account , it is giving error:

    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)".

    But  using windows authentication then it gives

    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 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".



    Any other configuration/Idea to resolve it?

    Thanks,
    Parmeshthee




    Learning .Net 3.5
    Friday, December 19, 2008 2:24 PM
  • Hi 

    Is this by any chance a SQL Server 2005 Express X64 edition ?  SQL Server X64 will not be able to load the ACE OLEDB 32 bit provider. And we do not have a 64 bit provider for Microsoft ACE OLEDB ?


    Bindesh V[MSFT]-SQL Developer Support
    Friday, December 19, 2008 3:55 PM
  • Hi

    I am also getting the same error on my 32 bit machine

    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)".

    However it works with the provider "Microsoft.Jet.OLEDB.4.0".

    How to resolve it?
    Wednesday, July 15, 2009 1:10 AM
  • Hi

    After reading registry entries for other providers, I have added a new entry to registry and it's working fine now Windows 7 32 bit. It seems that Microsoft does not want to populate ACE OLEDB provider.

    Create a .reg file with text below and replace MSSQL10 (10 = SQL Server 2008) with your MSSQL version and SQLADV with SQL Server instance name. Save the file and run. It'll add this entry to registry and you can use OPENROWSET with ACE OLEDB providers.



    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLADV\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001

    Wednesday, July 15, 2009 1:35 AM
  • Try run this:

    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

     Then try your query. I have met this issue before and this should works.

    Wednesday, July 22, 2009 7:09 AM
  • I have a very similar error linking to Office 2007 Excel spreadsheet from SQL 2005

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "eelist" 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 "eelist".

    AFter changes above I get this error:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EELIST" reported an error. The provider reported an unexpected catastrophic failure.
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EELIST". (Microsoft SQL Server, Error: 7399)



     I have everyone full access to the excel file linked to.

    Thursday, March 18, 2010 3:28 AM
  •  

    Thanks, THis helped me too but gets me to a new error:

     

    Msg 7308, Level 16, State 1, Line 1

    OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    • Proposed as answer by AkulD Friday, August 20, 2010 3:06 PM
    Wednesday, June 09, 2010 5:26 AM
  • I was getting "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" on 64-bit machine while importing data from MS Access to SQL Server 2005

    Execution of the following commands solved the problem:

    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

    Make sure:

    1.MS Access 2007 Installed

    2. Microsoft Access database engine 2010 installed.

     

    thanks
    Friday, August 20, 2010 3:17 PM
  • Quick question; trying to clear an issue with Msg 7308:

    This whole registry key adding thread, here:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb?prof=required

    As well as here:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

    Along with the image at:

    http://picasaweb.google.com/pandaatms/MSDN?feat=directlink#5317293760877787762

    does not solve the 7308 error report.

    But I concede I might be missing something:

    I see this image (images as helpers in forums are good, generally) and I compare it my Linked Servers tree and am baffled by the fact that I don't have either "Microsoft.Ace.OLEDB.12.0" or "Microsoft.Jet.OLEDB.4.0" as Provider, much less an option to pull up a panel and warm my tootsies by the "Allow inprocess" tickbox fire.

    End there. If there's no Provider showing up in the interface of MSSMSE, then I probably don't have the right version, right? Often there are TYPE IIs in ssmse ("executed successfully") when in fact nothing happens. Is this the case with sp_mmset_oledb_prop?

    Can't find anything anywhere on this.

    ~~~~~~~~~~~

    WindowsServer2008R2 (OS-6.1.7600)
    SQLServer2008Express Advanced Services (10.0.2531.0)
    MDAC(6.1.7600.16385)

    64-bit

    ~~~~~~~~~~~~

    1. Could someone tell me where these system files exist so I can check the version?

    2. Since the error is outright "cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode" is there a similar stored procedure (as in the above citations) to "RECONFIGURE" this setting?

    Related:

    http://social.answers.microsoft.com/Forums/en-US/w7programs/thread/40de1d9d-98f2-4526-8e6a-a4e611b92760

    So, the question might be appended again:

    If I as developer can easily overcome an issue with changing the Advance Settings in the compiler to output the x86 executable so that it successfully accesses the Microsoft.Jet.OLEDB.4.0 driver, how come my Office 2007 installation hasn't been updated in support service pack form to access the same driver?

    Isn't this the same access issue? As stated above, the image provided woud appear to me to be one found on a 32-bit box. Not 64-bit. So in conclusion, this information is not relevant to Windows Server 2008. At least!

    ~~~~~~~~~~~~~~~~~~~~~~

    100926:

    Four days have passed and there's been no activity on this thread.

    Searching for more information regarding the advise given above and the discovery that the image provided is for a 32-bit operating system NOT a 64-bit OS, leads to the conclusion that the fix provided should be reported as abuse.

     

    • Edited by lypoten Sunday, September 26, 2010 4:58 PM abuse reported
    Wednesday, September 15, 2010 10:06 PM
  • UNMARKED AS ANSWER:

    See below ...

    Tuesday, September 21, 2010 6:38 PM
  • Have a look this article...... Its gave detail abt Microsoft.ACE.OLEDB.12.0' reported an error. Access denied.

    http://www.ashishblog.com/blog/importexport-excel-xlsx-or-xls-file-into-sql-server/

    Sunday, May 01, 2011 1:17 PM
  • Another alternative is to use SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Launch: SSMS --> Object Explorer --> Right Click Database --> Tasks --> Import Data

    Wizard video: http://www.youtube.com/watch?v=9Wmdhnx1niU


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Proposed as answer by Subash.S Monday, March 26, 2012 3:54 PM
    • Edited by Kalman Toth Friday, September 28, 2012 10:24 PM
    Wednesday, May 11, 2011 3:30 PM
  • Out of curiosity... how are you connecting to SQL box? locally or using another machine?

    I find that when using locally to execute the following:

    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\ServerMachine\Access.mdb')...[Table]

    Will work fine.

     

    However using another machine's SSMS connected to that SQL box will error with:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\ServerMachine\Access.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".

    So again... how are you executing the statement? via local SQL box or remote?

    Tuesday, September 20, 2011 8:29 PM
  • This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

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

    ..I found an article here...

    http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

    ..which suggested I give Everyone full permission on this folder..

    C:\Users\<SQL Service account name>\AppData\Local\Temp

    And hey presto!  My query suddenly burst into life.  I punched the air in delight.

    Edwaldo

    Sunday, January 08, 2012 5:29 PM
  • Indeed.. It worked for me as well. Thanks Edwaldo for sharing it.
    Saturday, January 21, 2012 6:22 PM
  • after registering the provider, add xml and the script will like this:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 xml;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');
    remeber to close the excel before you execute it. good luck.

    Wednesday, February 15, 2012 3:08 PM
  • Hi All,

    I have to Open Excel by Using T-SQL:

    I used Below queries:

    select * FROM OPENROWSET(
      'Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=C:\Test.xlsx;HDR=YES',
      'SELECT * FROM [Sheet$]')

    This query is keep running.


    select * FROM OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=C:\Test.xls;HDR=YES',
      'SELECT * FROM [Sheet $]')

    OR

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;DATABASE=C:\Test.xls', 'Select * from FROM [Sheet$]')

    This query is giving below error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.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 1

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

    I tried this also but still displaying same error;

    USE [master] 

    GO 

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 

    GO 

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1 

    GO

    Anyone can tell me what could be problem & What could be solution

    http://sqlyoga.com/2009/12/sql-server-how-to-read-excel-file-by.html

    http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx


    Thanks Shiven:) If Answer is Helpful, Please Vote

    • Proposed as answer by u828576 Tuesday, July 29, 2014 6:38 AM
    Saturday, May 19, 2012 9:55 AM
  • Create a .reg file with text below and replace MSSQL10 (10 = SQL Server 2008) with your MSSQL version and SQLADV with SQL Server instance name. Save the file and run. It'll add this entry to registry and you can use OPENROWSET with ACE OLEDB providers.

    gavrav,

    i have couple of questions  on above statement
    1. Create .reg file (create where ?? and then what)
    2. replace MSSQL10  (replace this WHERE, i mean where will i find MSSQL10(10=Sql server 2008) statement  for replacement)
    3. save the file (whcih file)

    Thanks in Advance

    Parixit

    Wednesday, August 29, 2012 10:30 AM
  • For Excel 2013

    USE [master] 
    GO
    sp_configure 'show advanced options', 1
    reconfigure
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1 
    GO 

    Important:

    Although not recommended, try running SQL Server Management Studio as Administrator (Right Click and Run As Administrator). Tried while offline and worked perfectly.

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 'Excel 12.0;Database=D:\test\Test.xlsx;HDR=YES ', [Sheet1$])

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 'Excel 12.0;Database=D:\test\Test.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

    -- The below also worked like a charm

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\test\Test.xlsx;HDR=YES ', [Sheet1$])

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\test\Test.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

    Hope it helps anyone.

    Evrard


    • Edited by Evrard Kouadio Wednesday, October 30, 2013 6:00 PM Consistency
    • Proposed as answer by theo-plusplus Saturday, December 07, 2013 3:31 PM
    Wednesday, October 30, 2013 5:59 PM
  • Important:

    Although not recommended, try running SQL Server Management Studio as Administrator (Right Click and Run As Administrator). Tried while offline and worked perfectly.

    Tried everything else and different variations of the solution but running as administrator sorted it for me. Running Excel 2013 and SS2012 64-bit
    • Proposed as answer by John Garbes Sunday, March 30, 2014 1:27 PM
    Saturday, December 07, 2013 3:41 PM
  • This finally did the trick for me!
    Sunday, March 30, 2014 1:28 PM