BCP error 'Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file'

• tony.walsh

  SSC Rookie

  Points: 35

  More actions

  March 18, 2013 at 4:15 am

  #271784

  The code is designed to geneate a csv file from a query using BCP. It works okay when run against a database on my PC. But I get the error when running it againast a database on a separate server.

  @Database varchar(20),

  @Filepath varchar(50),

  @Filename varchar(30),

  @Username varchar(10),

  @Password varchar(10),

  @Server varchar(50),

  @Query varchar(1000)

  /*

  -- works fine with these settings

  set @Database = 'BPC'

  set @Username = 'fw'

  set @Password = 'fw'

  set @Server = 'TONY_DESKTOP\SQLEXPRESS'

  */

  -- fails for these settings

  set @Filepath = 'C:\Development\GENERAL\Childrens_Index\'

  set @Database = 'reports_worcs'

  set @Username = 'fw'

  set @Password = 'd3v3lop'

  set @Server = 'ss-lon1.dev.corelogic.local'

  set @Filename = 'Address_YP_'+ @v_id+'_'+replace(replace(CONVERT(VARCHAR(19), @v_processed_on, 120),':',''),' ','_')

  set @Query = 'master.dbo.xp_cmdshell ''bcp "select * from ' + @Database + '..ci_address_details where address_id in ('+'''''ADDRESS_ID'''''+',''''' + @v_id + ''''')" queryout ' + @Filepath + @Filename + '.csv' + ' -U' + @Username + ' -P' + @Password + ' -c -t"|" -S' + @Server + '''';

  exec (@Query);