bcp ~ "the name specified is not recognized as internal or external command, operable program or batch file"

• Nicholas Wang

  Default port

  Points: 1463

  More actions

  December 29, 2004 at 1:35 pm

  #60627

  I'm trying to write this bcp to input a txt file into a table, the txt file is field delimited by the | character,

  bcp database1.dbo.user_purchase_table in D:\purchase-data.txt -n -t| -Sserver01 -Uusername -Ppassword

  It gives the following error: "the name specified is not recognized as internal or external command, operable program or batch file"

  I know bcp works though, coz I wrote another to run a sproc on the same server and output the result into a txt file, and it works:

  bcp "EXEC database1.dbo.usp_newPurchase" queryout D:\newPurchase.txt -c -t, -Sserver01 -Uusername -Ppassword

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  December 29, 2004 at 2:09 pm

  #535337

  The pipe '|' is a DOS keyword that is used to redirect output.  I think you're going to have to use a format file to make this work (check the documentation on using format files with BCP).

   

• Nicholas Wang

  Default port

  Points: 1463

  More actions

  December 29, 2004 at 4:10 pm

  #535355

  Thanks!

  I created the format file, and also I used the "bcp format file automatic generation script" found here on SQLServerCentral.com, however, I got error from both:

  SQLState = S1000, NativeError = 0

  Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column

  SQLState = S1000, NativeError = 0

  Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file

  Searched but didn't find any previous posts on this... any idea?

• Linda Johanning

  SSCertifiable

  Points: 6727

  More actions

  December 30, 2004 at 8:51 am

  #535408

  You don't necessarily need a format file, just double-quotes around the pipe:

  EXECUTE master..xp_cmdshell 'BCP pubs..authors out C:\authors.txt /c /Sservername /T -n -t "|" '

  I've gotten the error before when BCP wasn't located in the default location on the C drive.  You may just need to add the full path:

  EXECUTE master..xp_cmdshell 'E:\Mssql\Binn\BCP pubs..authors out C:\authors.txt /c /Sservername /T -n -t "|" '

  Linda