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.
james1977Asked:
Who is Participating?
 
james1977Author Commented:
I got there in the end, I just had to give the folder its own share and connect directly to that from the server root instead of going through loads of folders.

Hopefully this helps someone else.

SELECT * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bfd-Server\db_creation\postalsectors.xls; HDR=YES', 'SELECT * FROM [Sheet1$]')
0
 
lozzamooreCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.