Post reply

BCP syntax issue...

  • January 10, 2007 at 9:11 am

    #153374

    OK I am attempting to create a simple datafile from a table using BCP for testing purposes and I am following what is BOL under the title "Copying Data From a Query to a Data File"

    I have the following bcp command line created.

    bcp 'SELECT TextData FROM tb_PositivePayTextFileData' queryout c:\Authors.txt -c -Sxxxxx -Usa -Pxxx

    I took out some private information but the servername, username and password information are correct. When I copy the above code into a command line and run it I get the following error.

    copy direction must be either 'in', 'out' or 'format'

    I am not sure what that means...I have tried a couple different formatting options but nothing seems to work..it seems like I need to be adding more parameters to my bcp command line but I am not seeing anything in BOL or on google that is a real help.

    Any ideas?

    Thanks in advance.

    leeland

  • January 10, 2007 at 9:56 am

    #682448

    Not sure what I missed but I got it to finally work...

    bcp "SELECT TextData FROM tb_PositivePayTextFileData" queryout c:\Authors.txt -c -Uxx -Pxxxx -Sxxxxxx

    Probably a syntax issue I just missed...either that or the order of the parameters.

  • January 10, 2007 at 5:44 pm

    #682584

    Nope... you using single quotes in the first attempt... you correctly used double quotes in the second attempt.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 11, 2007 at 7:05 am

    #682671

    This was my first time creating something for BCP...I got the concecpt now   So it doesn't matter what order the parameters are in?

     

    Also, last question...the reason I was toying around with BCP to try a different approach with a process that we are having an issue with.

     

    When the records are written to the file, the very last record gets written and then a I believe a carriage return & line feed occurs and puts a blank line after that record.

    For me that causes a problem with another application that the file gets imported because it sees that blank line is an invalid record.

     

    Is there a away to determine an End Of File Terminator so it doesn't put a blank line at the end of the file?  The same thing happens with DTS...you can take the Line Feed option away but then the formatting is all messed up.

  • January 11, 2007 at 6:05 pm

    #682804

    Yes, some of the BCP command line is order sensitive... most of the "switches" are not but the other stuff typically is.

    So far as the "other" application goes, most folks would agree that the industry standard is to terminate all lines, including the last line, the same way.  I'd have to say "fix the other app" but maybe you can't.

    Have you tried using the -r parameter of \r?  Than would give a carriage return without line feeds at the end of each line.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2007 at 6:16 am

    #682880

    Hi Jeff,

     

    I had not tried the -r parameter...I will look into that.  As for the application it is a vendor app and I don't think it can be changed but that is one thing that is being investigated.

     

    Thank you for the information I will give that parameter a try and see how it comes out.

     

    Leeland

    EDIT:

    I tried some of the parameters, but they do not work...my guess is I cannot have my cake and eat it to. The reason being, I need the carriage return and line feed to keep the formatting of the file. So...since I cannot find a way to stop that last night from getting a line feed on it...the application person is going to have to find a way to deal with the extra line.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply