bcp error handling

Hello
I am using bcp.exe to transfer data between remote servers and need a way to
do the error handling. What I need is simple - just to know whether the copy
was successful or not, because it needs to be in transaction with other
operations. There are situations when the return value of the exe is always
0 and nothing gets written into the error log ( for example copying from
file to table that violates a constraint ). All that came to me was to parse
the log file ( option -o ) for strings like 'error' and 'failed' but this
doesn't look like a great idea. And yes, I am using bcp in both directions,
so bulk insert will not do.
Any ideas of how to find whether the copy was successful?
(sql server 2000 sp3, in case it matters)
Thanks
Plamen
"Plamen Doykov" <p_doykov@.code.bg> schrieb im Newsbeitrag
news:uoZ1ear1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am using bcp.exe to transfer data between remote servers and need a
way to
> do the error handling. What I need is simple - just to know whether the
copy
> was successful or not, because it needs to be in transaction with other
> operations. There are situations when the return value of the exe is
always
> 0 and nothing gets written into the error log ( for example copying from
> file to table that violates a constraint ). All that came to me was to
parse
> the log file ( option -o ) for strings like 'error' and 'failed' but
this
> doesn't look like a great idea. And yes, I am using bcp in both
directions,
> so bulk insert will not do.
> Any ideas of how to find whether the copy was successful?
> (sql server 2000 sp3, in case it matters)
We settled with the output parsing also. That's what we use in Java:
private static final Pattern ERROR_PATTERN =
Pattern.compile(
"^\\s*(?:SQLState\\s*=\\s*([^,]+?)\\s*,\\s*NativeError\\s*=\\s*([+-]?\\d+)
\\s*|" +
"Error\\s+=\\s*+(.*?))$", Pattern.MULTILINE );
/**
* Extract error BCP messages from the given process output.
*
* @.param bcpOutput the output to extract the messages from.
* @.return a List of {@.link String} with the messages.
*/
protected static List extractErrorMessages( String bcpOutput ) {
List messages = new LinkedList();
Matcher m = ERROR_PATTERN.matcher( bcpOutput );
SqlServerError err = null;
while ( m.find() ) {
String message = m.group( 3 );
if ( message == null ) {
/*
* no message => first line:
* SQLState = 23000, NativeError = 3604
*/
err = new SqlServerError();
err.setSqlState( m.group( 1 ) );
err.setNativeError( StringParsing.string2int( m.group(
2 ) ) );
}
else {
/*
* second line:
* Error = [Microsoft][ODBC SQL Server Driver][SQL
Server]Doppelter Schl?ssel wurde ignoriert.
*/
err.setMessage( message );
messages.add( err );
err = null;
}
}
return messages;
}
Kind regards
robert