Using OPENQUERY with EXCEL LinkedServer

[flavin]

Hi,

I have created a linked server for an Excel worksheet as
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\aflavin\ExcelFies\SampleDatabase.xls',
NULL,
'Excel 5.0'
GO

When trying to create a SP to pull all data from the file


Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT * FROM DETAILS'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

Can anyone tell me how to get around this problem.
Thanks,
Ann

 

[tlbroadbent]

Try this.[ol][li]Open the spereadsheet in Excel.
[li]Select the first row containing the column headers.
[li]Select Insert | Name | Label from the menu bar.
[li]Verify that the first row is listed in the label range of the Label Ranges dialog and click OK.
[li]Close the spreadsheet and try to access it via SQL Server.[/ol] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[flavin]

I have done as you said, but am still getting the same error. I selected the first row A1 to U1 which contain the column headers and added this row 1:1 in the label range of the Label Ranges dialog.
Thanks,
Ann