Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John