Post reply

OPENROWSET ERROR

  • November 28, 2005 at 9:28 am

    #95954

    Hi, I'm trying to access an excel spreadsheet using jet.4 and openrowset

    I am issuing the command

    SELECT * FROM OPENROWSET(

    'Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=c:\access\RCApptCardevent.xls',

        '[sheet1$]')

    However I am receiving the error

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 

    [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.]

    SQL Server is running under the domain administrator's logon and the Excel spreadsheet exists with a sheet named sheet1.

    Has anybody any ideas as to what esle could cause this problem

    Thanks

     

     

     

     

  • November 28, 2005 at 1:38 pm

    #606217

    try:

    SELECT * FROM OPENROWSET(

    'Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=c:\access\RCApptCardevent.xls',

        'Select * from [sheet1$]')

  • November 29, 2005 at 9:22 am

    #606446

    No luck I'm afraid Peter, I am able to add the access database as a linked server using 'Microsoft.Jet.OLEDB.4.0' as the driver, but using a dynamic OPENROWSET with same it's not interested

    Cheers

     

  • November 30, 2005 at 7:45 am

    #606736

    The syntax works for me. If you can do it by linked server, you should be able to use OPENROWSET.

    Check the sheet name in your Excel file;

    Try to use 'Excel 5.0;Database=...' instead of 'Excel 8.0;Database=...'

     

  • November 30, 2005 at 7:48 am

    #606738

    Did you really try my script?

    Your code gives the following error on my machine:

    Server: Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing a query for execution against OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.

    [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:repare returned 0x80040e14].

    My code just works fine on my machine.

    It seems you didn't try.

  • January 23, 2006 at 4:23 am

    #616368

    Just for completeness and for reference purposes, the reason this wasn't working was due to a typo by me in the filename, once I corrected this the original syntax worked fine

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply