• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1438
  • Last Modified:

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

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?
0
glentek
Asked:
glentek
  • 2
2 Solutions
 
derekkrommCommented:
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
0
 
bamboo7431Commented:
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 :)
0
 
derekkrommCommented:
glen, any updates?
0
 
glentekAuthor Commented:
I ended up using the bcp command line utilility instead of "BULK INSERT" SQL command.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now