I have created a linked server in Microsoft SQL Server 2005 to an Excel datasource. When I try to access the data via simple select (ie select * from EXCELONENGSVCDB...AllData), I get the following error message:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCELONENGSVCDB" does not contain the table "AllData". The table either does not exist or the current user does not have permissions on that table.
I selected all cells in the spreadsheet and labeled it AllData but this does not seem to work. The same type of link from a SQLEXPRESS server works fine.
I do not have an Excel app on the Microsoft SQL Server 2005 machine as I do on the machine that houses the SQLEXPRESS server. Is it necessary to have the Excel app or just the Excel file?
The OS is Windows Server 2003. The SQL server version is 9.0.
Any help is greatly appreciated!