5

I have a job that has a stored procedure that runs BCP to QUERYOUT some data.

If I run the QUERYOUT command by itself, it works.

However, if I try to run it in a JOB, it creates the file but "hangs" and the data is never put in the file. This hangs forever so I usually terminate the BCP.exe.

My question is:

How do I get a SQL job to run BCP to do a QUERYOUT and have the permissions to do so?

The QUERYOUT is going to the C:\ drive (so nothing fancy or anything).

This problem is driving me nuts and from reading online, it looks like the user that runs the jobs needs permissions to the folder. The folder isn't protected or anything.

The owner of the SQL job is part of the SYS ADMIN group in SQL.

Any help would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JJ.
  • 9,580
  • 37
  • 116
  • 189

1 Answers1

13

check what user is assigned to SQL Server Agent service. Open services.msc, locate the SQL Server Agent and check Logon properties. There will be either a LocalSystem user (unlikely, based on what you have described) or another user.

Check that the user has "Write" access to the folder where you are trying to write the BCP dump.

cha
  • 10,301
  • 1
  • 18
  • 26
  • Okay this is weird. This user is set to our Admin account. I just created another job, with a new stored procedure, doing a BCP queryout and it worked! Do you think it might have to do with my original job having a transaction and other sprocs in it? – JJ. Feb 07 '13 at 02:51
  • is it possible that in your Job steps you have not specified the correct database or login? – cha Feb 07 '13 at 02:56