none
OPENROWSET, OPENDATASOURCE failure --- Excel spreadsheet

    Question

  • Attempting to SELECT * from an Excel spreadsheet on the network but having some error issues.  The SProc doesn't fail in the check, but on execution it gives the error:

    "OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].
    Msg 7399, Level 16, State 1, Procedure GetExcelData, Line 5
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."


    Here's the T-SQL:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[GetExcelData]
     
    AS Set Nocount On
    BEGIN
    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
    'Excel 8.0;DATABASE=C:\S.R. Data Prep_WB.xls', 'SELECT * FROM [Sheet1$]')   
    
    Set Nocount Off
    return 0
    end
    



    Our IT department tech has told me that the latest Jet provider is already installed, so I am not certain where to go from here.


    jfc
    Monday, April 27, 2009 4:44 PM

All replies

  • Monday, April 27, 2009 5:04 PM
  • Ana,

    First, thanks for the quick reply.  However, I did notice at the bottom of the article it states that this workaround applies to SQLServer 7.0 and 2000.  I originally didn't mention this (my bad, sorry about that) is that we are running SQLServer 2005.

    Would this workaround apply to this version of SQLServer as well?

    Thanks again!

    j
    jfc
    Monday, April 27, 2009 5:18 PM
  • Hi James,

    FileName should not have space.

    I found that you have used File Name: " S.R. Data Prep_WB.xls"

    Please change your file name too: "S.R.DataPrep_WB.xls".

    So now your procedure is:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[GetExcelData]
     
    AS Set Nocount On
    BEGIN
    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
    'Excel 8.0;DATABASE=C:\S.R.DataPrep_WB.xls', 'SELECT * FROM [Sheet1$]')  

    Set Nocount Off
    return 0
    end


    Let me know if it helps you.

    Thanks,


    Tejas Shah
    SQL YOGA
    Tuesday, April 28, 2009 7:57 AM
  • Hi James,

    FileName should not have space.

    I found that you have used File Name: " S.R. Data Prep_WB.xls"

    Please change your file name too: "S.R.DataPrep_WB.xls".



    Tejas Shah
    SQL YOGA


    Thanks for the suggestion.  However, after changing the excel filename (removing any spaces), I still return the same error.

    j
    jfc
    Wednesday, April 29, 2009 2:36 PM
  • Hi,

    I am able to get data from Excel with the file name (after removing space). 

    Please make sure that "SheetName" is entered correctly.

    Please post your query now.

    Thanks,

    Tejas Shah
    SQL YOGA
    Wednesday, April 29, 2009 2:39 PM
  • Hi,

    I am able to get data from Excel with the file name (after removing space). 

    Please make sure that "SheetName" is entered correctly.

    Please post your query now.

    Thanks,

    Tejas Shah
    SQL YOGA

    Now it cannot find the sheet name I have specified!   arrrrrrr!

    The sheet name is clearly named in the .xls, and correctly named in the SProc but it cannot find it.


    [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].
    Msg 7399, Level 16, State 1, Procedure GetExcelData, Line 5
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
    jfc
    Wednesday, April 29, 2009 3:23 PM
  • If I remove the [] block around the SheetName, it seems to be able to locate the sheet ("Traps"), but now it throws a syntax error in the FROM clause.


    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[GetExcelData]
     
    AS Set Nocount On
    BEGIN
    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls', 'SELECT * FROM Traps$')   
    
    Set Nocount Off
    return 0
    end



    jfc
    Wednesday, April 29, 2009 3:27 PM
  • SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls', 'SELECT * FROM [Traps$]')

    With this statement, I get an error:

     

    [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Traps$'.  Make sure the object exists and that you spell its name and the path name correctly.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].
    Msg 7399, Level 16, State 1, Procedure GetExcelData, Line 5
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.


    jfc
    Wednesday, April 29, 2009 3:30 PM
  • Hi James,

    I seen that your query is wrong.

    Don't need to specify SELECT * FROM with OPENROWSET, when we are reading from Excel file.

    Please change query to:

    SELECT
     *
    FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0' ,
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls' , '[Traps$]' )
    It should work. Let me know if it works or not

    Thanks,

    Tejas Shah
    SQL YOGA
    Thursday, April 30, 2009 4:37 AM
  • Hi James,

    I seen that your query is wrong.

    Don't need to specify SELECT * FROM with OPENROWSET, when we are reading from Excel file.

    Please change query to:

    SELECT
    
     *
    FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0' ,
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls' , '[Traps$]' )
    It should work. Let me know if it works or not

    Thanks,

    Tejas Shah
    SQL YOGA

    Thanks Tejas,

    Here's the error I get when I paste the SQL you provided:

    [OLE/DB provider returned message: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandPrepare::Prepare returned 0x80040e14].
    Msg 7321, Level 16, State 2, Procedure Monitoring_SelectSRTraps, Line 6
    An error occurred while preparing a query for execution against OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.


    Here's the updated query:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[Monitoring_SelectSRTraps]
    AS
    SET NOCOUNT ON;
    BEGIN
    SELECT
     *
    FROM
     OPENROWSET
    ('Microsoft.Jet.OLEDB.4.0'
    ,   
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls'
    , '[Traps$]'
    )
    
    END

    jfc
    Thursday, May 07, 2009 3:55 PM
  • Hopefully you have solved this already, but for the sake of future readers:  the sheet name shouldn't be in quotes.  That is, you should have
      SELECT
    *
    FROM
    OPENROWSET
    ('Microsoft.Jet.OLEDB.4.0'
    ,  
    'Excel 8.0;DATABASE=C:\S.R.Data_Prep_WB.xls'
    , Traps$)

    The problem I'm running into is that this works on some of our servers but not others... grr!

    Cheers,
    Dave
    Friday, May 15, 2009 4:34 PM
  • Dave, thanks a lot, for those who are using OpenRowSet in an Excel connection manager as I was this tip sorts out the problem, just remove the quotes if your sheet has spaces.

    Thanks
    Rob
    Wednesday, May 20, 2009 4:11 PM