SQL Server Agent Job calling bcp

Topic

New Reply

We are upgrading from SQL2000 to SQL2008. I currently use bcp in several SQL Server Agent jobs to pull out data files to make available to other agencys. The tested job fails in SQL2008. Review of the bcp guidlines show the output file now must be enclosed in double quotes [“] however the job still fails and the error information is not helpful.

[indent]Execution of job ‘Your Abstract query’ failed. See the history log for details’ [/indent]

The only ‘history’ log I can find is the Server Manager Windows Logs under Application; where the following warning is displayed:

[indent]”SQL Server Scheduled job ‘Your Abstract query’ ()- Status: Failed – Invoked on: 2010-08-31 09:02:29 – Message: The job failed. The job was invoked by User sa. The last step to run was step 2 (Create Abstract file). The job was requested to start at step 2(Create Abstract file).”[/indent]

This bcp statement will execute using the command line:
[indent]bcp db.dbo.AbstractOIR out “\ftprootAbstract.rpt” /c /S /U sa /P [/indent]

Any assistance would be appreciated.
Bob

Reply | Quote

Replies

As the user is SA the job will most likely run using the SQL Agent credentials. Do these credentials allow file creation / write in the directory specified?

cheers, Paul

Reply | Quote

As the user is SA the job will most likely run using the SQL Agent credentials. Do these credentials allow file creation / write in the directory specified?

cheers, Paul

Thank you Paul. The network guys did have to help us with several permission issues and it appears to have cured the problem.

Bob

Reply | Quote

Network guys helping with permissions is usually a recipe for disaster!

cheers, Paul

Reply | Quote