CODEWITHSUNDEEP
sqlsql-serverbulkinsert
E. Monk
E. Monk

Reputation: 378

Cannot Bulk Load Error Code 3. Procedure working on one machine only

I have created a stored procedure of which takes two parameters: 1)@tablelocation 2)@tablename.

The procedure is pretty simple - it bulk imports a CSV file, performs a few modifications, and then renames it to the @tablename parameter. See below for execute script:

DECLARE @return_value int

EXEC    @return_value = [dbo].[spLocationTableImport]
        @tablelocation = N'C:\TestFile',
        @tablename = N'TestTable'

SELECT  'Return Value' = @return_value

GO

My procedure works perfectly when ran on one machine - however when I attempt to run it from another machine, I receive the error:

Cannot bulk load because the file "C:\TestFile.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

I am logged in on both machines with the same credentials so I do not think this is a permission issue (at the user level anyways). Any help on this issue is greatly appreciated. Thank you.

Upvotes: 1

Views: 6178

Answers (1)

Tom H
Tom H

Reputation: 47392

When SQL Server has to work with a directory it is using the directory on SQL Server. You can't put a file on your local C: drive and then ask SQL Server to import it through BULK INSERT.

You'll need to be able to copy the file up to the SQL Server or provide your own import functionality through a local application (or potentially a web application) that connects to the SQL Server and imports the data.

Alternatively, you could give SQL Server access to your local machine and have it import through UNC paths, but that's prone to all sorts of errors not to mention that every PC would have to be set up and maintained for this.

Also, keep in mind security if you're going to let other people on local machines import data into your SQL database.

Upvotes: 1

Related Questions