MS SQL Server BULK INSERT can't access remote input file

Posted on 2007-07-20
Last Modified: 2008-03-04
I am running an MS SQL Server on my home office workstation.  I have a VPN connection to a remote network.  I am running a vbscript on the remote server that establishes a connection to my SQL Server and attempts to execute a "BULK INSERT" command.  The input file is on the remote server, but because the BULK INSERT is executed on my local SQL Server, I am getting a permissions error as the SQL Server attempts to access the file.  The SQL Server is running under the LocalHost user, which does not have access to the file.  However, there is no user on my local workstation that would be recognized by the remote computer on the remote network.  Any ideas or suggestions?
Question by:glentek
    LVL 15

    Assisted Solution

    If you put the file on an FTP site on the remote server, you can create a DTS package on your local workstation to download the file from the FTP site and then run the bulk insert.

    - derek
    LVL 4

    Accepted Solution

    Here's what I wrote to the folks in my company regarding BULK INSERT:
    The file has to be accessible from the SQL Server box by the SqlService account; this can be accomplished in one of the following 3 ways:
    - creating a share on the file server and referring to the file by share name;
    - Adding SqlService account to the Administrators group on the file server and referring to the file by full name( \\servername\e$\Data\...\filename.txt ) - obviously this wouldn't work in your situation
    - Manually copying the file to the DB server and referring to it by its local path

    I found that the last option is usually the easier one :)
    LVL 15

    Expert Comment

    glen, any updates?

    Author Comment

    I ended up using the bcp command line utilility instead of "BULK INSERT" SQL command.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now