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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to stored-procedures

Examples related to samba

Examples related to sql-server-agent

Examples related to bulk-load