Using OPENQUERY with LinkedServer (Excel)

[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

I now need to write a Stored Procedure that pulls all the data in the excel file. I am trying to use OPENQUERY but don't know how to write the query. What do I use in the FROM part of the query?

SELECT *
FROM OPENQUERY(ExcelSource, 'SELECT * ?????')

Thanks in advance,
Ann

 

[tlbroadbent]

The missing part of the syntax is a worksheet name.

Select * From Openquery(excelsource, 'Select * From QuarterlySales')

Make sure that you've added a remote login for the linked server. Use sp_addlinkedsrvlogin.

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

I found that also had to correct the JET 4.0 registry key for the system database. It did not contain the complete path to the system.mdw file. I updated the key in question using Regedit.

KEY: HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines
VALUE: C:\Program Files\Common Files\SYSTEM\system.mdb Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

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

 

[flavin]

I have added the remote Login but am now getting this error message.

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.

 

[tlbroadbent]

Open the Workbook and verify the existence of column headings on the spreadsheet DETAILS? Terry
Please review faq183-874.

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

 

[flavin]

The cells A1 to U1 are being used to hold the text that are the column headings. However they do not have any special formatting. Is it required for these cells to be formatted to make them "column headings"?