Retrieving data from Excel linked server

[raphanid]

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!

[rmiao]

Tried with openquery?

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

[raphanid]

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.