Link to home
Start Free TrialLog in
Avatar of james1977
james1977

asked on

Connect to Excel file on another computer on same network.

Hi
I am using Sql Server 2005 to build a system to automate setting up a database for a web product we sell. To do this I need to insert a list of postal sectors and area names from an excel spread sheet into an SQL database. The spreadsheets have to be stored on a different computer than the one SQL server is on.

I've got the system working as long as the spreadsheet is on the same computer as SQL but struggling to connect to one on another computer. I am using this to connect:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\postalsectors.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Which works fine, but when I try:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\SERVER\wwwroot\postal_sector_spreadsheets\postalsectors.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

I get the error message:

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)".

Thanks for any help.
James.
Avatar of lozzamoore
lozzamoore
Flag of United Kingdom of Great Britain and Northern Ireland image

Could be to do with remote permissions, or local permissions on C:\TEMP.

Check this link out, it might help.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=505236&SiteID=17
ASKER CERTIFIED SOLUTION
Avatar of james1977
james1977

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial