Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Ole DB Source throwing error while query for Excel Openrowset

Domanda Ole DB Source throwing error while query for Excel Openrowset

  • Thursday, February 21, 2013 11:12 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I am trying to query the excel using the OPEN Rowset

    I have configured the connection manager correctly (test connection is working).

    When I am trying to write query in the source during the preview it is giving me syntax error as shown in the screenshot, I have tried all the ways from google but not working can anybody suggest anything in this :

    "Syntax error in FROM Clause"

    SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML;Data Source=C:\Users\sumit.kumar.dua\Desktop\Sumit\Common.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

    Thanks in advance

    Sumit

     

All Replies

  • Thursday, February 21, 2013 11:14 AM
     
     
    Sign In to Vote
    0
    Please re-submit your screen shot as th eimage is missing...

    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 www.lite-solutions.co.uk

     
  • Thursday, February 21, 2013 12:44 PM
     
     
    Sign In to Vote
    0

    There is some problem I am not able to post image :

    This is the error message I am getting when clicking on preview

    Exception from HRResult: OxC0202009 
    Error at Data Flow Task[OLE DB Source[2]]: SSIS Error code DTS_E_OLEDBError. An OLE DB error has occured. Error code:0x80040E14.An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80040E14 Description: "Syntax error in FROM clause".

    The query which I am using I have posted above

    Thanks

    Sumit

     
  • Friday, February 22, 2013 6:34 AM
    Moderator
     
     
    Sign In to Vote
    0
    Does that spreadsheet have a sheet called "Sheet1"?

    Todd McDermid's Blog Talk to me now on

     
  • Friday, February 22, 2013 7:49 AM
     
     
    Sign In to Vote
    0

    Yes it is having, Are there are any enviornment prerequisites required ?

    Thanks

    Sumit

     
  • Monday, February 25, 2013 7:07 PM
    Moderator
     
     
    Sign In to Vote
    0

    No.

    Have you tried a "table" selection, instead of a "SQL Command"?  Can you select "Sheet1"?

    Todd McDermid's Blog Talk to me now on

     
  • Tuesday, February 26, 2013 6:14 AM
     
     
    Sign In to Vote
    0

    Yes I tried to preview the data by selecting the table its working fine the problem is with the query or something is wrong.