dcsimg
Results 1 to 3 of 3

Thread: Retrieving data from Excel linked server

  1. #1
    Join Date
    May 2007
    Posts
    2

    Retrieving data from Excel linked server

    I'm trying to set up a linked server as in the article

    http://www.databasejournal.com/featu...0894_3085211_3

    I use the following SQL to set up a linked server with Excel and attempt to retrieve data. The c:\temp\authors.xls is downloaded from the second page of the article above.

    EXEC sp_addlinkedserver 'EXCEL',
    'Excel',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\temp\authors.xls',
    NULL,
    'Excel 8.0',
    NULL
    GO
    SELECT * FROM EXCEL...Sheet1$
    GO

    The query sets up the linked server OK, and retrieves the field names, but no data is returned. The results in Management Studio are a list of column names:

    au_id au_lname au_fname phone address city state zip
    contract

    then an error:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Unknown".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT `Tbl1002`.`au_id` AS `Col1004`,`Tbl1002`.`au_lname` AS `Col1005`,`Tbl1002`.`au_fname` AS `Col1006`,`Tbl1002`.`phone` AS `Col1007`,`Tbl1002`.`address` AS `Col1008`,`Tbl1002`.`city` AS `Col1009`,`Tbl1002`.`state` AS `Col1010`,`Tbl1002`.`zip` AS `Col1011`,`Tbl1002`.`contract` AS `Col1012` FROM `Sheet1$` `Tbl1002`" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL".

    Does anyone know how to resolve this? I'm using Windows Authentication to connect to a SQL server (9.0.3054) instance on localhost, and am running from a test database query window.

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with openquery?

    select * from openquery(excel, 'select * from sheet$1')

  3. #3
    Join Date
    May 2007
    Posts
    2
    Thanks for the reply, I've got it working now.

    I tried running OpenQuery, and it was taking several minutes to run. So I restarted the machine, and now both SELECT * FROM OPENQUERY (excel, 'SELECT * FROM [Sheet1$]') and SELECT * FROM EXCEL...Sheet1$ both work as expected.

    Not sure what caused it to fail though. I've been using automation to test different ways of writing Excel files (from VB), perhaps that confused it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •