locked
Can not access Excel file using OpenRowset RRS feed

  • Question

  •  

    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

     

    Wednesday, August 8, 2007 11:03 PM

Answers

  • 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

    Saturday, September 1, 2007 12:20 AM

All replies

  • Can you post the text of your query?
    Friday, August 10, 2007 9:57 PM
  • Here you go ...

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

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

    Friday, August 10, 2007 10:00 PM
  • 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?
    Saturday, August 11, 2007 12:05 AM
  • Hello Anton,

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

    Thanks.

     

    Saturday, August 11, 2007 12:16 AM
  • Connecting to the same server with the same credentials?
    Tuesday, August 14, 2007 6:33 AM
  • 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

    Saturday, September 1, 2007 12:20 AM
  •  

    i am having similar scenario.. I can execute the query 3 -4 time after that i get 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)"."

     

    I got to know from a forum that restarting of SQL Server service resolves this issue and it is true!

    It solved my problem. But in real life we can't restart sql server every times !!!!

    So, is this a Bug in the SQL Server 2005 ?

     

    Please any body, can help me?

    Monday, November 12, 2007 7:50 AM