December 29, 2004 at 1:35 pm
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
December 29, 2004 at 2:09 pm
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).
December 29, 2004 at 4:10 pm
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?
December 30, 2004 at 8:51 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply