Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002

    Unanswered: Return Codes for Sybase BCP

    Hi guys
    I was wondering if you could help me.
    I have been trying to do some test relating to BCP.
    I have been trying to BCP into a database table some data (484 rows) from a data file.
    I have manipulated 2 rows from this data file in order to fail the BCP process. The BCP process bulk copies 482 rows into the table as expected.

    How can I determine the return code for this BCP process I had executed ? Does BCP return any return code despite finding bad data along the way as I have explained above ?

    I have been developing a Perl program to determine this return code and it always return me zero value. I am assuming zero value means the BCP was successful. Is this correct ?

    I would be most grateful for any advice
    Many thanks

  2. #2
    Join Date
    Mar 2001
    Lexington, KY
    I am not sure if it returns an error code, check the documentation here:;pt=1806?target=%25N%15_10086_START_RESTART_N%25

    Your most reliable bet would be to read the error log it creates:;pt=6057#X


  3. #3
    Join Date
    May 2002

    Thumbs up Re: Return Codes for Sybase BCP


    U can use Unix scripts for your above query.
    This is how you can do this:

    bcp .... > outputfile
    egrep a word from output file that you see when 2 rows are not copied to the Sybase server. If you find an occurrence of this word more than once, raise a unix error and stop your processes by returning this raised error value.

    If the above logic is not clear, let me know and I will put the exact syntax in this discussion forum.
    Last edited by rayvid123; 05-19-02 at 12:51.

  4. #4
    Join Date
    Nov 2009

    How can I return status when import success? any parameter can be used? or work around provide for me ?


  5. #5
    Join Date
    Mar 2009
    Sydney, Australia
    Yeah, Sybase made one big mistake (for unix people) in the beginning and they have never corrected it. None of the utilities follow the unix standard and return a proper (non-zero or negative) return code upon error.

    The correct way to find out if (a) bcp failed or (b) some number of rows failed to be inserted is
    1 check the bcp error log (text output). The minimum is to grep it for "^Msg" and "error". My bcp scripts use 3 grep pattern files (error; exception and non-error msgs I want to pick up).
    2 Use the error_file option "-e my_failed_rows.txt". bcp will store the failed rows in it. Count (wc) the lines and you're home and hosed.
    3 My bcp scripts use a plain text control file that stores info such as original bcp-out rowcount per partition, etc. I generate the same after the bcp-in; and then diff the two control files. That may be overkill for most people, but my scripts are commercial products.


    But, hang on a minute. If you are using perl, you do not have to do all this (the above is correct for shell scripts), because perl has a beautiful and complete interface with Sybase. Look for DB-Library or CT-Library, IIRC it is called sybperl. Not only can you run all your SQL commands directly from perl via a dbhandle (far superior to scripting an isql session), you can use the bulk-copy facility (bcp interface) and insert at bcp speeds. And get errors thrown in perl, direct to your errohandle. Eliminates the need to run bcp via a shell script or perl.
    Last edited by Derek Asirvadem; 11-09-09 at 11:30. Reason: Larry Wall Rules !
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts