Cannot Query Excel Linked Server

Hello,

I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;

-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts

I have the linked server configured as follows;

-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\\Cdnbwfin1\Data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls;Persist Security Info=False

When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')

The following message appears, and no results are returned;

[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?

Thank you for your help!

cdun2

Hi,

how are you connecting to the SQL Server while using this query ? If you are connecting to the server using a SQL Server login, the process will try to impersonate the SQL Server account (that one that runs the service of SQL Server) to acces the network file. If the Service account is not priviledged to access the file nor the network share, the process will fail. Could you describe your used enviuronment for a better understanding if the above description is your problem ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de