none
OPENROWSET with Excel 2007 -- 7330 Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Question

  • I am trying to use OPENROWSET with an Excel 2007 file. It seems to be partially working. Here is what I am doing:

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

    Msg 7399, Level 16, State 1, Line 1
    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.
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Note that I do get back a results set with the correct field names from the Excel spreadsheet, but no rows!!

    As an experiment I also saved the file in Excel 2003 format and the following query ran just fine, returning all rows:
    select * FROM OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=C:\Test.xls;HDR=YES',
      'SELECT * FROM [DataLoad$]')

    This is not really a good long term solution though, as I was not planning to switch back to Excel 2003 anytime soon.

    So, any ideas on how to get this to work in Excel 2007?? I messed around with the query and the data, changing the queried ranges etc., but the results were the same every time.

    Are there new extended properties I can fool around with? What else could be causing this?
    Tuesday, March 10, 2009 7:18 PM

Answers

  • Awesome -- this worked. I scripted the actions of setting these properties as follows:

    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 did not need to restart the server after making the changes.

    I searched the registry afterwards, and found that it created the following entries, although I would recommend anyone else with this problem use the T-SQL commands above:

    Windows Registry Editor Version 5.00 
     
    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.EXP2008\Providers\Microsoft.ACE.OLEDB.12.0] 
    "AllowInProcess"=dword:00000001 
    "DynamicParameters"=dword:00000001 
     
     


    Thanks for your help!

    • Marked as answer by lex3001 Thursday, March 26, 2009 5:21 PM
    Thursday, March 26, 2009 5:20 PM
  • http://picasaweb.google.com/pandaatms/MSDN?feat=directlink#5317293760877787762

    I take a screenshot for your reference, if this still can't work, please inform me. Thanks.

    • Proposed as answer by Naomi N Thursday, January 19, 2012 1:25 PM
    • Marked as answer by lex3001 Thursday, January 19, 2012 4:01 PM
    Thursday, March 26, 2009 12:57 AM
    Moderator
  • First, download and install 2007 Office System Driver: Data Connectivity Components .

    Now you can query Excel 2007 files using the following T-SQL:

    select * FROM OPENROWSET (

      'Microsoft.ACE.OLEDB.12.0' ,

      'Excel 12.0;Database=C:\Test2.xlsx;HDR=YES' ,

      'SELECT * FROM [Sheet1$]' )

     

    If you run into Error 7330 (and just the headers come back, no records), which might be either a 64-bit OS or a Vista problem (I'm not sure which), then you can workaround the issue by running the following T-SQL commands to configure how SQL will use the ACE driver:

    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

     

    Now try again... this solved it for me.

    • Marked as answer by lex3001 Tuesday, July 07, 2009 7:10 PM
    Monday, July 06, 2009 4:54 PM

All replies

  • Maybe this is a Vista specific issue?

    http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-newbie/3024/Querying-an-Access-2007-Database-using-OpenRowSet

    Anyway, I am still looking for a solution.
    Wednesday, March 11, 2009 4:49 PM
  • If you use sql authentication to log on the server, make sure the account to run the sql service has the access right to this file. Or if you use windows authentication, make sure the account has the access right to this file. I verified this issue on Windows 2008, using SQL Server 2000 + Excel 2007.
    Tuesday, March 17, 2009 7:05 AM
    Moderator
  • Thanks for replying, however this is not the problem. If the SQL Server process could not read the file at all, it would also not be able to generate the list of columns correctly for the result set from the headers in the Excel file (which it is doing -- it is just not returning any of the data).

    Also, I am using Windows Authentication and testing with my account and have access to both the Excel 2007 and 2003 files...
    Monday, March 23, 2009 4:14 PM
  • Could you give me more detail information as this:
    1) SQL Server version: 2000, 2005 or 2008 and SP number.
    2) OS: Vista RTM, Vista SP1 or others?
    3) Proccessor: X86 or X64?
    4) Use SQL authentication or Windows authentication?

    Thanks
    Wednesday, March 25, 2009 7:33 AM
    Moderator
  • I have enrolled the Devs to look at this issue.
    A quick work around is import the following registry file to make your work:
    (Here is the file content)

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001
    "DynamicParameters"=dword:00000001

    Wednesday, March 25, 2009 7:50 AM
    Moderator
  • This is in the development environment, as follows:

    Ying Lin - MSFT said:

    Could you give me more detail information as this:
    1) SQL Server version: 2000, 2005 or 2008 and SP number.

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (WOW64)

    2) OS: Vista RTM, Vista SP1 or others?

    Vista Ultimate SP1 64-bit edition

    3) Proccessor: X86 or X64?

    Intel Core2 Duo CPU T7300 2Ghz (Dell D630)

    4) Use SQL authentication or Windows authentication?

    Windows Authentication


    Thanks



    Wednesday, March 25, 2009 4:53 PM
  • Ying Lin - MSFT said:

    I have enrolled the Devs to look at this issue.
    A quick work around is import the following registry file to make your work:
    (Here is the file content)

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001
    "DynamicParameters"=dword:00000001


    I tried importing the above registry script, restarted the SQL Server instance, but the problem persists.
    I have several instances of SQL 2005 and 2008 varying editions installed. My instance in this case is called EXP2008. I located a registry entry for EXP2008 (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\EXP2008\) that appeared to be the correct registry location for settings for this instance. The only sub node in the registry was MSSQLServer. I imported the registry file (changing MSSQL.1 to EXP2008 first), restarted the SQL Server, and tried again, but the OPENROWSET still failed with the same error message.
    Wednesday, March 25, 2009 7:40 PM
  • http://picasaweb.google.com/pandaatms/MSDN?feat=directlink#5317293760877787762

    I take a screenshot for your reference, if this still can't work, please inform me. Thanks.

    • Proposed as answer by Naomi N Thursday, January 19, 2012 1:25 PM
    • Marked as answer by lex3001 Thursday, January 19, 2012 4:01 PM
    Thursday, March 26, 2009 12:57 AM
    Moderator
  • Awesome -- this worked. I scripted the actions of setting these properties as follows:

    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 did not need to restart the server after making the changes.

    I searched the registry afterwards, and found that it created the following entries, although I would recommend anyone else with this problem use the T-SQL commands above:

    Windows Registry Editor Version 5.00 
     
    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.EXP2008\Providers\Microsoft.ACE.OLEDB.12.0] 
    "AllowInProcess"=dword:00000001 
    "DynamicParameters"=dword:00000001 
     
     


    Thanks for your help!

    • Marked as answer by lex3001 Thursday, March 26, 2009 5:21 PM
    Thursday, March 26, 2009 5:20 PM
  • Hmm i have doen all of the above i ended up with a query that seems to run but takes forever and doesn't fill the table. The stement seems fine to me

    SQL- 2008
    Vista Business

    Insert

     

     

    Into dbo.XLS_Conversions
    select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\temp\Cnv_DailyImport.xlsx;HDR=YES' , 'Select * from [Report$]')
    Tuesday, June 30, 2009 9:14 PM
  • lex3001,

    Dear friend I face a problem when I use openrowset in order to insert from excel 2007 to sql. It seems I have been facing the same problem that encounter you some time before. I see the solution you suggested but I don't found sp_MSset_oledb_prop stored procedure you suggested to import the Microsoft.ACE.OLEDB.12.0 Provider. So please help me.
    Monday, July 06, 2009 1:51 PM
  • Did you solve the above problem concerning the openrowset case. When I run it, it only returns the headers in the excel sheet. It seems it is trying to fetch but could not perform all the tasks.
    Monday, July 06, 2009 1:57 PM
  • First, download and install 2007 Office System Driver: Data Connectivity Components .

    Now you can query Excel 2007 files using the following T-SQL:

    select * FROM OPENROWSET (

      'Microsoft.ACE.OLEDB.12.0' ,

      'Excel 12.0;Database=C:\Test2.xlsx;HDR=YES' ,

      'SELECT * FROM [Sheet1$]' )

     

    If you run into Error 7330 (and just the headers come back, no records), which might be either a 64-bit OS or a Vista problem (I'm not sure which), then you can workaround the issue by running the following T-SQL commands to configure how SQL will use the ACE driver:

    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

     

    Now try again... this solved it for me.

    • Marked as answer by lex3001 Tuesday, July 07, 2009 7:10 PM
    Monday, July 06, 2009 4:54 PM
  • A followup note... on another project, another server, I ran into a similar issue all over again on my Windows 7 64-bit development machine. Same error 7330, but this time not even the column names were returned. I ended up resolving it by installing x86 SQL Server and setting the service to run as Local System instead of Network Service. I had already tried making the files/folders accessible to Everyone just to be sure it wasn't a permissions issue. In fact, I was able to run xp_cmdshell with "more <filename>" and see the contents of the file. When I get some time I might try and narrow the solution down more. I had also run the following commands, not sure if all them were necessary in the end:

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    exec sp_configure 'Advanced', 1
    RECONFIGURE
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    exec sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    GO
    Sunday, December 27, 2009 8:13 PM
  • Hey guys

    I have a similar question but not exactly. Here is my situation. I have "Foreach Loop Container" that iterates though Excel files located on the network. On each iteration, I connect to each of the files using an "OLE DB Source" connection manager to retrieve file metadata like the names of the Excel Spread Sheets and so forth. After all those iterations, I run a SQL-Server stored procedure that calls the OPENROWSET on each of those tables in each of the Excel files to import the data.

    All seems to be working fine for the first excel file. Once am trying to call the OPENROWSET on the second file (which happens to be the last in this case) I get an SSIS error. 

    If I stop the process right prior to openning the second Excel File and manually attempt to access the file, I get a "Read only" indicator telling me the file is locked by a process. Obviously that's the reason I can't access the file.

    So, my question is, how can close the last Excel connection created by the iteration? I've tried:

    • ConnectionManager.ReleaseConnection
    • ConnectionManager.Dispose

    Here is the code so you can take a pick:

     

    Dim intFileIndex As Integer = CType(Dts.Variables.Item("COUNTERA").Value, Integer)

    Dim strExcelMeta As String = Dts.Variables.Item("EXCELMETA").Value.ToString

     

    Dim strExcelData As String = GetCurrentExcelData(strExcelMeta, intFileIndex)

     

    Dim intSheetCount As Integer = GetSheetCount(strExcelData)

     

    Dim objConnMan As ConnectionManager

     

    'Close all open connections to Excel files.

     

    For Each objConnMan In Dts.Connections

     

    If objConnMan.Name.ToUpper.Trim = "EXCEL_OLE" Then

    MsgBox(

    "EXCEL_OLE", MsgBoxStyle.SystemModal)

    objConnMan.ReleaseConnection(

    Nothing)

     

    ElseIf objConnMan.Name.ToUpper.Trim = "EXCEL_COMBINED" Then

    MsgBox(

    "EXCEL_COMBINED", MsgBoxStyle.SystemModal)

    objConnMan.ReleaseConnection(

    Nothing)

    objConnMan.Dispose()

     

    End If

     

    Next

    Friday, June 11, 2010 11:04 PM
  • 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
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
    GO
    Ivan V
    Monday, February 07, 2011 4:57 PM
  • 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
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
    GO
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
    GO
    Ivan V


    Ivan V
    Monday, February 07, 2011 4:58 PM
  • hi Ivan,

     

    I have tried all the Scripts above and not worked for  me.

    Server is -2008 64-bit,

    SQL - 2008 64-bit,

    no Office 2010 installed, but installed AcessDatabaseEngine_x64, Microsoft Access Runtime 2010.

     

    and query:

    SELECT * --INTO dbo.BulkLead 

    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

        'Excel 14.0;HDR=YES;IMEX=1;Database=C:\LeadTemplate.xls',

        'SELECT * FROM [Sheet1$]');

     

        but Error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

    Msg 7303, Level 16, State 1, Line 2

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

    Please help.

    Monday, June 06, 2011 9:51 AM
  • Hi,

     

    I Also had various problems, like 7303 and 7399 errors with de ace.oledb driver in the following configuration

    Win7 professional , 32 bit

    SQL Server 2008 R2, Developer edition.

    Access Database engine version 2010

    Test query for xlsx:

    Select * From Openrowset('Microsoft.ACE.OLEDb12.0','Excel12.0 Xml;HDR=YES;Database=C:\Test\MijnTest.Xlsx','SELECT * FROM [Blad1$]')

     

    Only Jet.Oledb.4.0 worked, although the ACE.OLEDB was visible in SQL as linked server provider

     

    Everything on my system seemed ok, checked all versions, settings, rights etc.

     

    I tried all suggestions but noting helped.

     

    Finally I deinstalled the Access Database Engine 2010 and replaced it with the 2007 version and voila...

    After that I deinstalled the 2007 version and installed the 2010 version again and it kept working.

    Go figure........

     

    The only thing is that the JET.OLEDB does not work anymore. But that's not a problem, I can use ALE.OLEDB instead.

     

    Thursday, September 01, 2011 9:40 AM
  • 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



    Saturday, May 19, 2012 8:07 AM