SQL Server Bulk Insert fails (Network related)

Question

I'm fairly new to SQL Server.

I'm trying to bulk insert into a table, using the command in SQL Server Management Studio (2005):

BULK INSERT Table1 FROM 'c:\text.txt' WITH (FIELDTERMINATOR = '|')

I get the error:

Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "c:\text.txt" does not exist.

I'm positive the file actually exists.

I get the feeling that it is looking for the file on the local hard drive for where ever the server is. Is that the case? If so, how do you generally solve this problem? (to note, I've tried specifying the network address of my PC when entering the location of the text file, but I get a permission error. Also, I know in advance that my company doesn't allow files to placed on a server).

Answer 1

Yes, it will look for the file on the SQL server itself.

If you can map a network drive to the C drive of your SQL server, then you can just copy the file over before running the bulk insert.

If you absolutely can't get any access to the server's file system, then you can look at doing something like this:

• write a program that reads your text file and inserts the contents into single record in a temporary table that has a Text field, perhaps using a stored procedure
• have the program execute the bcp command to export the data from the temporary table into a text file on the SQL server's local file system, to a folder that the account under which the SQL service is running has write permission
• have the program run a bulk insert command specifying the path to the text file on the server
• delete the text file and the temporary table

Answer 2

All file-related activities on SQL Server (like backup, restore, bulk load etc.) will always work only on the server's drives.

How should the SQL Server on a separate machine be able to read your local harddisk? Would you even want a remote server to read your harddisk? REALLY?!?

Unfortunately, all the SQL Server file-related activities only work on physically present drives, e.g. on internal drives or USB-attached drives on the server. Mapping a network share won't do - SQL Server's disk-related commands usually don't find those mapped drives either :-(

So you really have to physically get the files onto the server's disks one way or another - or (temporarily) attach an external harddisk to the server.

Marc

Answer 3

Have you created a share drive on your machine that the server can see? If so then you just need to refer to the path including your machine name instead of C:

Answer 4

SQL Server does not have an SQL statement that reads data from the client end (as the other posters have pointed out). Other RDBMS products do implement this (eg. the Postgres COPY statement lets you specify a file on the server or a file on the client that is read by the db connectivity library on the client side).

You can achieve moving data from a file on the client to a table on SQL Server using the bcp command line program.

bcp lets you copy data from a local file to a table on the server, or from a table (or select query) on the server into a local file. For example:

bcp servername.dbname.tablename in c:\temp.txt -T -c

will copy a tabbed delimited file (temp.txt) into the specified table (assuming the file contains the right number of columns).

I am not sure if this helps, but it is the only way to move data from a client file to a server table without giving the server some sort of access across the network to the data file on client.

Answer 5

I'd agree that it's a problem with the file being on your C drive, and not the server's drive.

If it's a permissions issue, have you tried creating a file share on your workstation that the server does have permissions to read from? Maybe something like \YourWorkstation\SQLFile, and then granting everyone (or Guest, depending on how your network permissions are set up) read access on it?

If you can't create the share on your laptop, or you can't grant rights to it for some reason, is there a file share somewhere in the office that you do have rights to, and that SQL can also read from? Maybe a NAS or a "Common" network folder?