Setting up a SQL job that uses Bulk Load

Question

Setting up a SQL job that uses Bulk Load

Esanders323

SSC Enthusiast

Points: 185
More actions
February 11, 2016 at 2:45 pm

#304661

Hi,
I have a stored procedure that reads a flat file from a shared folder. It uses bulk load to read this file and put the contents in a table in my database. I want this stored procedure to run once a day, so I set up a job for it. However, the job is failing and giving me the following warning:
Executed as user: [User]. You do not have permission to use the bulk load statement. [SQLSTATE 42000] (Error 4834). The step failed.
I did give the user bulk admin rights, so I'm not sure why it won't let me do this.
Here is the config for the job step
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlquaker Say Hey Kid Points: 697 More actions · Answer 1

There are couple of options you can try. Please check this out:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/48621876-11a0-468c-95b4-2a682f0134aa/bulk-insert-not-working-through-an-agent-job?forum=transactsql

-Regards

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Have you tried removing the "Run As" user?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Esanders323 SSC Enthusiast Points: 185 More actions · Answer 3

If I remove the "Run As" then the NT SERIVCE\SQLSERVERAGENT cannot find the file that I'm trying to bulk load. My initial assumption was because the folder is on a network drive and it most likely didn't have permissions to access it.

I tried running it as a sqlcmd as you can see below:

If I view the job history, it says the following:

Executed as user: [User I'm logged in as]. Msg 4861, Level 16, State 1, Server [localhost], Procedure sp_SYS_import_raw_csv, Line 78 Cannot bulk load because the file "Z:\Schedule\iefin.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.). Process Exit Code 0. The step succeeded.

The iefin.txt is on a network drive, but it is using the correct user. I can open that file if I open a windows cmd. I can also navigate to it. The stored procedure also works fine if I just run it manually.

If i'm able to manually see/open the file and run the stored procedure, then I'm led to believe that there is some sort of security issue. However, I'm not sure why. It is executing as my user account.

Thanks

sqlquaker Say Hey Kid Points: 697 More actions · Answer 4

Have you tried YourMachineName\InstanceName on sqlcmd command on job step?

-Regards

Esanders323 SSC Enthusiast Points: 185 More actions · Answer 5

I found the issue. I was trying to use the Z drive when I needed to specify the full path in the stored procedure.

Thanks for the help