Cannot bulk load because the file could not be opened. Operating System Error Code 3

Question

I'm trying to set up a Stored Procedure as a SQL Server Agent Job and it's giving me the following error,

Cannot bulk load because the file "P:\file.csv" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105). [SQLSTATE 42000] (Error 4861)

Funny thing is the Stored Procedure works just fine when I execute it manually.

The drive P: is a shared drive on Windows SQL Server from LINUX via Samba Share and it was set up by executing the following command,

EXEC xp_cmdshell 'net use P: "\lnxusanfsd01\Data" Password /user:username /Persistent:Yes'

Any help on this would be highly appreciated

Determinable

Looks like a permissions issue. The SQL Server Agent usually runs as a different user / with different permissions compared to when you run it manually. Does the Agent work if the file is in a local directory? Is the the Agent also running the net use command? – Induna

The agent is not running the net use command. I haven't tested that as I don't have RDP access to the SQL Server – Determinable

Well, does it work if you add the net use cmd to the Agent job? Without that, I'm pretty sure the P: drive will only be available in your username's Windows session, not the Agent's. – Induna

I can add the net use command but the only question then would be it will try to map the directory every time the job runs, so for example the second time it runs and when it tries to assign P: Drive again it will give an error. How do I get this one sorted? – Determinable

Run a net use /delete P: to remove it after the bulk load – Induna

Using net use command in the SP didn't work – Determinable

Answer 1

I do not know if you solved this issue, but I ran into the same issue. If the instance is local you must check the permission to access the file, but if you are accessing from your computer to a server (remote access) you have to specify the path in the server, so that means to include the file in a server directory, that solved my case.

example:

BULK INSERT Table
FROM 'C:\bulk\usuarios_prueba.csv' -- This is server path not local
WITH 
  (
     FIELDTERMINATOR =',',
     ROWTERMINATOR ='\n'
  );

Shaky

Thanks, you pointed out exactly my issue and solved my 15 minutes of 'The file is there, why can't you read it?'. – Hensel

Thanks @Shaky what's the permission of the file? - right click -> security -> edit -> who has full control. Thank you! – Fabre

Answer 2

To keep this simple, I just changed the directory from which I was importing the data to a local folder on the server.

I had the file located on a shared folder, I just copied my files to "c:\TEMP\Reports" on my server (updated the query to BULK INSERT from the new folder). The Agent task completed successfully :)

Finally after a long time I'm able to BULK Insert automatically via agent job.

Omophagia

Answer 3

I have solved this issue,

login to server computer where SQL Server is installed get you csv file on server computer and execute your query it will insert the records.

If you will give datatype compatibility issue change the datatype for that column

Wallford

I'd upvote this 100 times if I could... I usually do my development on the SQL Server on my development box, so a local reference "C:\temp\" usually works... But I was testing on another SQL Server. THANK YOU!!! – Nub

Answer 4

I did try giving access to the folders but that did not help. My solution was to make the below highlighted options in red selected for the logged in user

Differentiable

That may be something you reconsider.. ideally you want to only give minimum permissions, and only add them as needed for this upload (and remove them as soon as the upload is done). As it is, this person could totally delete you entire database/delete tables/or do just about anything accidentally. – Nub

Answer 5

Using SQL connection via Windows Authentication: A "Kerberos double hop" is happening: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote "\\NETWORK_MACHINE\". Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To resolve the issue you need to enable constrained delegation for the SQL Server service account. See here for a good post that explains it quite well

SQL Server using SQL Authentication You need to create a credential for your SQL login and use that to access that particular network resource. See here

Bezant

Answer 6

It's probably a permissions issue but you need to make sure to try these steps to troubleshoot:

• Put the file on a local drive and see if the job works (you don't necessarily need RDP access if you can map a drive letter on your local workstation to a directory on the database server)
• Put the file on a remote directory that doesn't require username and password (allows Everyone to read) and use the UNC path (\server\directory\file.csv)
• Configure the SQL job to run as your own username
• Configure the SQL job to run as sa and add the net use and net use /delete commands before and after

Remember to undo any changes (especially running as sa). If nothing else works, you can try to change the bulk load into a scheduled task, running on the database server or another server that has bcp installed.

Induna

How do I configure the SQL job to run as your own username? I went to login and during UserMapping I selected msdb and provided permissions for SQLAgent. Is that right? – Determinable

I don't have access to SQL Server right now but depending on your version, it might look like this i31.tinypic.com/zjdqh3.jpg – Induna

I tried using a SQL Server Authentication to run the SQL job but I get the following error, The server principal "sqldev01" is not able to access the database "mydatabase" under the current security context. the sqldev01 account has db_owner access to mydatabase to which I'm trying to insert the csv file. It also has db_owner access to Master database and also has Bulkadmin role. Please help – Determinable

Looks like you are in troubleshooting hell. Can you try one of the other suggestions? – Induna

Answer 7

I would suggest the P: drive is not mapped for the account that sql server has started as.

Animism

The P: drive is not mapped for the account that sql server has started as implies the windows user, but not explicitly. Just verifying you mean the windows user? – Priscillaprise

Answer 8

This just happened to me, and it turned out it was because the file the SQL command was trying to read from wasn't on the same server as the SQL database. Once I put the file on the same server (and changed the query, of course), the query worked.

Waldack