DBRemote aborting on error causing missing message

[Former Member]

Is DBRemote supposed to abort when error occurs while executing messages? I am using SA12 and with some errors while receiving and executing messages it stops. Leaving the rest of the files intact. This is causing a missing message on the next replication (which is really not the case, but caused by the deleted processed files and the exist of the non processed files).

Is there an option to cancel this behaviour or is this a bug?

As far as I can remember, ASA9 version of dbRemote did not abort the receival like this.

Running in batchmode, here's an example of the log output:

I. 2012-05-08 13:43:41. SQL Remote Message Agent Version 12.0.1.3554
I. 2012-05-08 13:43:41. 
I. 2012-05-08 13:43:41. Copyright © 2001-2012, iAnywhere Solutions, Inc.
I. 2012-05-08 13:43:41. Portions copyright © 1988-2012, Sybase, Inc. All rights reserved.
I. 2012-05-08 13:43:41. Use of this software is governed by the Sybase License Agreement.
I. 2012-05-08 13:43:41. Refer to http://www.sybase.com/softwarelicenses.
I. 2012-05-08 13:43:41. 
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-0)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-1)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-2)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-3)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-4)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-5)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-6)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-7)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-8)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0602694329-9)
I. 2012-05-08 13:43:41. Received message from "parentnode" (599-0602694329-0771199013-10)
I. 2012-05-08 13:43:41. Applying message from "parentnode" (599-0602694329-0602694329-0)
I. 2012-05-08 13:43:41. Applying message from "parentnode" (599-0602694329-0602694329-1)
I. 2012-05-08 13:43:41. Applying message from "parentnode" (599-0602694329-0602694329-2)
I. 2012-05-08 13:43:41. Applying message from "parentnode" (599-0602694329-0602694329-3)
I. 2012-05-08 13:43:42. Applying message from "parentnode" (599-0602694329-0602694329-4)
I. 2012-05-08 13:43:42. Applying message from "parentnode" (599-0602694329-0602694329-5)
I. 2012-05-08 13:43:42. Applying message from "parentnode" (599-0602694329-0602694329-6)
E. 2012-05-08 13:43:42. SQL statement failed: (-143) Column 'app_cl_no' not found
E. 2012-05-08 13:43:42. Skipping:
E. 2012-05-08 13:43:42. SELECT id, app_cl_no FROM DBA.accrual_fixed
E. 2012-05-08 13:43:42. Receiving messages failed
I. 2012-05-08 13:43:43. Execution completed

Message 7-10 are still present in the inbound folder when SQL Remote is completed. On next replication these are read and message 0-6 are treated as missing.

[VolkerBarth]

What errors are you getting (DBREMOTE -o log.txt) - SQL errors or file handling errors or database connection errors?

Are you running DBREMOTE in batch or continuous mode?

[Former Member]

batchmode. See update for log output

[regdomaratzki]

How did a SELECT statement end up in your message? Is this part of a larger passthrough script executed at parentnode?

What command line are you using for dbremote?

[Former Member]

The statements was passed on by a passthrough script and is only found in a test environment. The statement itself should not have been run at all. What made me curios is why and how this would stop the replication. Thanks to both of you 🙂

[VolkerBarth]

I would think the attempt to replicate data for a non-existing column sounds like a design error and should be clarified as such (and should only happen in a test environment). - As such, I don't know if this should stop the replication. Other statements causing errors (e.g. PK duplicates, missing FKs and the like) are skipped AFAIK, but don't stop replication. We generally use the REPLICATION_ERROR option to log errors but I don't think that this changes the way errors are handled itself.

(Yes, this is some kind of "know-it-all" advice, sorry!).

Besides that, have you tried to circumvent the error by one of these methods documented here? (Again, I would strongly recommend to test such issues in a test environment.)

[Former Member]

The comment from @Volker Barth is answering my question.

[VolkerBarth]

Glad it just happened in a test environment:)