Connect to Excel file on another computer on same network.

Posted on 2007-08-10
Last Modified: 2013-11-05
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:

'Excel 8.0;Database=C:\postalsectors.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Which works fine, but when I try:

'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.
Question by:james1977
    LVL 7

    Expert Comment

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

    Check this link out, it might help.

    Accepted Solution

    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.

    ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bfd-Server\db_creation\postalsectors.xls; HDR=YES', 'SELECT * FROM [Sheet1$]')

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now