BCP job fails

• PSB

  SSChampion

  Points: 13195

  More actions

  January 20, 2016 at 9:45 am

  #146016

  Hi,

  I am trying to copy SQL data from Server 1 to Server 2 using bcp. Because of the large number of rows in the source, we are fetching data from Server 1 into csv file using BCP. I am using SSIS Execute Process Task to run the bcp commands. It works fine from command prompt from the destination server 2 but when I create SQL job on Sever 2 and try to run it as a job ,I get error

  I performed the following steps :

  1)bcp command from SSIS : BatchProcesses in an AD account given read access to Server 1 particular database BoxQA)

  "SELECT * FROM BoxQA.dbo.MSP_Project_View" queryout "C:\IntegrationBCPData\Projects.csv" -S abc\SPCONTENT -T -c -U BatchProcesses -P PAssword1!

  2)I have enabled xp_cmdshell on Server 2

  EXEC sp_configure 'show advanced options', 1

  GO

  --To update the currently configured value for advanced options.

  RECONFIGURE

  GO

  -- To enable the feature.

  EXEC sp_configure 'xp_cmdshell', 1

  GO

  -- To update the currently configured value for this feature.

  RECONFIGURE

  GO

  3) Created job

  DECLARE @job NVARCHAR(100)

  SET @job ='execute_bcp'

  EXEC msdb..sp_add_job @job_name = @job,

  @description = 'Execute bcp command',

  @owner_login_name = 'sa',

  @delete_level = 1

  EXEC msdb..sp_add_jobstep @job_name = @job,

  @step_id = 1,

  @step_name ='Command Shell Execution', @subsystem = 'CMDEXEC',

  @command = 'bcp "SELECT * FROM BoxQA.dbo.MSP_Project_View" queryout "C:\IntegrationBCPData\Projects.csv" -S abc\SPCONTENT -T -c -U BatchProcesses -P PAssword1!',

  @on_success_action =1

  EXEC msdb..sp_add_jobserver @job_name =@job

  EXEC msdb..sp_start_job @job_name = @job

  4) Error message

  Message

  Executed as user: NT Service\SQLSERVERAGENT. SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'DIR\1455PMO003$'. Process Exit Code 1. The step failed.

  5) When I execute xp_cmdshell 'whoami.exe' I get output :

  nt service\mssqlserver

  NULL

  Please help!

  Thanks,

  PSB