51

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

user1345260
  • 2,151
  • 12
  • 33
  • 42
  • 1
    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? – acfrancis Oct 21 '13 at 10:50
  • 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 – user1345260 Oct 21 '13 at 10:53
  • 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. – acfrancis Oct 21 '13 at 11:02
  • 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? – user1345260 Oct 21 '13 at 11:04
  • 1
    Run a ``net use /delete P:`` to remove it after the bulk load – acfrancis Oct 21 '13 at 11:08
  • Using net use command in the SP didn't work – user1345260 Oct 21 '13 at 12:33

7 Answers7

63

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'
  );
Swati Bhartiya
  • 101
  • 1
  • 8
DuSant
  • 970
  • 12
  • 25
  • 2
    Thanks, you pointed out exactly my issue and solved my 15 minutes of 'The file is there, why can't you read it?'. – Dominique McDonnell Aug 22 '14 at 05:54
  • Thanks @DuSant what's the permission of the file? - right click -> security -> edit -> who has full control. Thank you! – YJZ Aug 08 '16 at 16:34
9

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.

Jose Santos
  • 107
  • 1
  • 3
8

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

anandchaugule
  • 901
  • 12
  • 20
  • 1
    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!!! – Danimal111 Mar 01 '19 at 17:53
2

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.

Yoosaf Abdulla
  • 3,722
  • 4
  • 31
  • 34
  • 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. – Danimal111 Mar 01 '19 at 19:00
2

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

Josh Harris
  • 446
  • 4
  • 8
1

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.

acfrancis
  • 3,569
  • 25
  • 21
  • 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? – user1345260 Oct 21 '13 at 13:28
  • I don't have access to SQL Server right now but depending on your version, it might look like this http://i31.tinypic.com/zjdqh3.jpg – acfrancis Oct 21 '13 at 13:32
  • 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 – user1345260 Oct 21 '13 at 15:41
  • Looks like you are in troubleshooting hell. Can you try one of the other suggestions? – acfrancis Oct 21 '13 at 16:35
1

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

Tim Napier
  • 11
  • 1
  • 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? – Urasquirrel Dec 20 '19 at 16:08