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
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.
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 !