[sql-server] Cannot bulk load because the file could not be opened. Operating System Error Code 3

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

The answer is


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


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


I dont know if you solved this issue, but i had 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'
  );

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.


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


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.

Best regards.


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

This is what you see when you right click and select the username you have logged in for windows authentication mode.


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table

Examples related to samba

Cannot bulk load because the file could not be opened. Operating System Error Code 3 How do you force a CIFS connection to unmount

Examples related to sql-server-agent

Cannot bulk load because the file could not be opened. Operating System Error Code 3 Could not obtain information about Windows NT group user How do I execute a stored procedure in a SQL Agent job?

Examples related to bulk-load

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