Post reply

Linked server for fetching data from Excel

  • May 7, 2010 at 11:35 pm

    #140077

    Hi,

    I am using Sql Server 2008 and MS Office 2007 on my PC. I have created linked server for fetching data from Excel sheet.

    For creating linked server I used below query..

    EXEC sp_addlinkedserver 'EXCEL_AUTHORS',

    @srvproduct = 'Excel',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @datasrc = 'C:\Documents and Settings\admin\My Documents\Downloads\authors.xls',

    @provstr = 'Excel 8.0;'

    GO

    I used below query after successful creation of linked server for Excel--

    SELECT *

    FROM OPENQUERY(EXCEL_AUTHOR, 'SELECT * FROM Sheet1$')

    GO

    It gives me error..as below..

    Msg 7321, Level 16, State 2, Line 2

    An error occurred while preparing the query "SELECT * FROM Sheet1$" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL_AUTHOR".

    Plz let me know solution for this problem.

    Regards,

    Nikhil

  • May 8, 2010 at 2:07 pm

    #1164608

    Specify 'EXCEL_AUTHORS' in openquery instead of EXCEL_AUTHOR.

  • May 8, 2010 at 2:37 pm

    #1164610

    First Excell 2007 uses a different ODBC driver than does previous versions of OFFICE products:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    Dowload and install above:

    Then to call Excel use:

    'Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;'

    Instead of:

    'Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;'

    Note: Not tested with a linked server but does work with OPENROWSET

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 10, 2010 at 1:47 am

    #1164868

    Thanks a lot.

Viewing 4 posts - 1 through 3 (of 3 total)

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