Link SQL 2005 server to a file 2

[micang]

SQL 2005

Hi All,

If I run the following code, it works no problem (provided that the D:\ is the drive of the server where SQL Server 2005 is installed).

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\trans.xls;Extended Properties=Excel 8.0')...Sheet1$

If however, I change the path to a location on the network like:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\server1\FOLDER\trans.xls;Extended Properties=Excel 8.0')...Sheet1$

I get the 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)".

Does anyone maybe know if this is a network problem or a SQL problem?

Any info appreciated.

Thanks

Michael

 

[SQLDenis]

1) can SQL server see that path?
2) if SQl server can see the path, does it have permissions on that folder?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

I suspect it's a permissions problem.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

Thanks guys, I will ask the IT people to check this. I get the feeling that more than likely it is a permission problem as you both suggest.

Many thanks.

Michael