Error during replication "Table 'request_queue' not found"

[VolkerBarth]

We are running dbremote.exe against a remote and consolidated database. We are intermittently getting the error:

E. 2017-01-23 10:22:15. SQL statement failed: (-141) Table 'request_queue' not found E. 2017-01-23 10:22:15. Skipping: E. 2017-01-23 10:22:15. INSERT INTO...

when running replication. The funny thing is we do not have a table or reference a table called 'request_queue'. We thought this might be a system table, but could not find it using:

select * from sysobjects where name like '%queue%'

We are wondering if this is a temporary table that is created by dbremote.exe whilst it is running and, if so, how this relates to the errors we are getting and what might be causing this? When the skipped SQL is run into the database in iSQL, no errors are reported and the inserts complete successfully.

In case it's relevant, we are running dbremote.exe on the consolidated database as a client connection (over TCP/IP) from the remote laptop.

The relevant parts of the log file follow below:

I. 2017-01-23 10:21:22. SQL Remote Message Agent Version 17.0.4.2100 I. 2017-01-23 10:21:22. I. 2017-01-23 10:21:22. Copyright © 2016 SAP SE or an SAP affiliate company. I. 2017-01-23 10:21:22. All rights reserved. I. 2017-01-23 10:21:22. Use of this software is governed by the SAP Software Use Rights Agreement. I. 2017-01-23 10:21:22. Refer to http://global.sap.com/corporate-en/our-company/agreements/index.epx. I. 2017-01-23 10:21:22. I. 2017-01-23 10:21:23. Received message from "LAP17" (0-0352772585-0354926703-0) I. 2017-01-23 10:21:23. Applying message from "LAP17" ... (0-0355183183-0355278716-3) I. 2017-01-23 10:22:14. Received message from "LAP17" (0-0355278716-0355309578-0) I. 2017-01-23 10:22:14. Applying message from "LAP17" (0-0355278716-0355309578-0) E. 2017-01-23 10:22:15. SQL statement failed: (-141) Table 'request_queue' not found E. 2017-01-23 10:22:15. Skipping: E. 2017-01-23 10:22:15. INSERT INTO DBA.DEVICE(DEVICE_ID,ACCOUNT_NUMBER,OFFICE_ID,PRODUCT_ID,

                          ORIGINAL_PO_TRAN_ID,ORIGINAL_INVOICE_TRAN_ID,ORIGINAL_ORDER_DATE,

                          DEVICE_MODEL,DEVICE_STYLE,DEVICE_SERIAL_NUMBER,DEVICE_TYPE,DEVICE_COLOR,

                          BATTERY_SIZE,DEVICE_COST,DEVICE_PRICE,DEVICE_DISCOUNT,EAR,DEVICE_STATUS,

                          DEVICE_LOCATION,REMOTE_SERIAL_NUMBER,RECEIVED_FROM_VENDOR_DATE,

                          DEVICE_FITTING_DATE,TRIAL_EXPIRATION_DATE,BASIC_WARRANTY_EXPIRATION_DATE,

                          LD_WARRANTY_TYPE,LD_WARRANTY_EXPIRATION_DATE,EXTENDED_WARRANTY_TYPE,

                          EXTENDED_WARRANTY_EXPIRATION_DATE,REPAIR_EXPIRATION_DATE,

                          LAST_REPAIR_PO_TRAN_ID,LAST_REPAIR_INVOICE_TRAN_ID,LAST_REPAIR_SENT_DATE,

                          LAST_REPAIR_RECEIVED_DATE,DATE_RETURNED,REASON_FOR_RETURN,

                          ACTIVELY_WEARING_FLAG,PURCHASE_TYPE,PAYMENT_TYPE,PRODUCT_MATRIX_ID,

                          HTL_500,HTL_1K,HTL_2K,HTL_3K,HTL_4K,MCL,UCL,CREATE_STAFF,CREATE_DATE,

                          ACTIVITY_TIMESTAMP,ACTIVITY_FLAG,MEDICAL_REFERRAL_ID)

                    VALUES (220782,152942,1000,51815,NULL,NULL,'2017/01/20',NULL,NULL,NULL,'Hearing

Aid',NULL,NULL,0,0,0,'Right','Delivered','With Patient',NULL,NULL,'2017/01/20',NULL,'2017/01/20',NULL,'2017/01/20',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'WT','14:26:17.199 2017/01/20','14:26:17.199 2017/01/20',1,167114) E. 2017-01-23 10:22:16. SQL statement failed: (-141) Table 'request_queue' not found E. 2017-01-23 10:22:16. Skipping: E. 2017-01-23 10:22:16. INSERT INTO DBA.DEVICE(DEVICE_ID,ACCOUNT_NUMBER,OFFICE_ID,PRODUCT_ID,

                          ORIGINAL_PO_TRAN_ID,ORIGINAL_INVOICE_TRAN_ID,ORIGINAL_ORDER_DATE,

                          DEVICE_MODEL,DEVICE_STYLE,DEVICE_SERIAL_NUMBER,DEVICE_TYPE,DEVICE_COLOR,

                          BATTERY_SIZE,DEVICE_COST,DEVICE_PRICE,DEVICE_DISCOUNT,EAR,DEVICE_STATUS,

                          DEVICE_LOCATION,REMOTE_SERIAL_NUMBER,RECEIVED_FROM_VENDOR_DATE,

                          DEVICE_FITTING_DATE,TRIAL_EXPIRATION_DATE,BASIC_WARRANTY_EXPIRATION_DATE,

                          LD_WARRANTY_TYPE,LD_WARRANTY_EXPIRATION_DATE,EXTENDED_WARRANTY_TYPE,

                          EXTENDED_WARRANTY_EXPIRATION_DATE,REPAIR_EXPIRATION_DATE,

                          LAST_REPAIR_PO_TRAN_ID,LAST_REPAIR_INVOICE_TRAN_ID,LAST_REPAIR_SENT_DATE,

                          LAST_REPAIR_RECEIVED_DATE,DATE_RETURNED,REASON_FOR_RETURN,

                          ACTIVELY_WEARING_FLAG,PURCHASE_TYPE,PAYMENT_TYPE,PRODUCT_MATRIX_ID,

                          HTL_500,HTL_1K,HTL_2K,HTL_3K,HTL_4K,MCL,UCL,CREATE_STAFF,CREATE_DATE,

                          ACTIVITY_TIMESTAMP,ACTIVITY_FLAG,MEDICAL_REFERRAL_ID)

                    VALUES (220783,152942,1000,51815,NULL,NULL,'2017/01/20',NULL,NULL,NULL,'Hearing

Aid',NULL,NULL,0,0,0,'Left','Delivered','With Patient',NULL,NULL,'2017/01/20',NULL,'2017/01/20',NULL,'2017/01/20',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'WT','14:26:17.215 2017/01/20','14:26:17.215 2017/01/20',1,167114) I. 2017-01-23 10:22:16. Received message from "LAP17" (0-0355309578-0355333231-0) I. 2017-01-23 10:22:16. Applying message from "LAP17" ... I. 2017-01-23 10:23:32. Sending message to "LAP29" (0-0421978944-0422023551-1) I. 2017-01-23 10:23:32. Sending message to "LAP30" (0-0421978944-0422023551-1) I. 2017-01-23 10:23:33. Execution completed

[VolkerBarth]

In case it's relevant, we are running dbremote.exe on the consolidated database as a client connection (over TCP/IP) from the remote laptop.

Speaking of "consolidated database" and "remote laptop" - what exactly does that mean? - Are you saying the dbremote process connected to the consolidated does not run on the machine hosting the consolidated database but on a different machine (which should not be a general problem as long as the different machine can access the message store...

What message system (FILE, FTP, SMTP, HTTP/HTTPS) do you use for the remotes?

[former_SQLA_member1694883]

Yes, exactly that, the dbremote.exe process connected to the consolidated database does not run on the machine hosting the consolidated database, but on a different machine. We are using the FILE messaging system. Everything is replicating fine apart from this intermittent error relating to this mystery table, where the data is skipped.

[VolkerBarth]

Hm, strange. Don't know whether there is such a temporary table used by SQL Remote (and if so, why it would issue an error here).

Can you find out what exact statements from the remote are applied just before/after the error message (via DBREMOTE -v) or via translating the log of the according remote? For the latter, DBTRAN -z -sr -ir might be helpful to restrict to a particular offset range and show DBREMOTE's distribution information...)

Does the error message only appear at the consolidated?

[Former Member]

In addition to Breck's observations and since this is occuring while applying a message (and given the single statement context involved is an insert into your "DBA"."DEVICE" table) I would recommend look into triggers defined on that object and any procedures, functions, or other table object referenced there (and triggers on any table objects referenced by/cascaded from those ... etc. )

Probably the quickest way to identify the exact source of this error would be to capture it in a request log. By using either -zr all (with -zo) or calling sa_server_option( 'requestlogging', 'all'), you should be able to get at the exact PSM-capable object and statement that is throwing this error.

This error is basically being thrown at the time of semantic and syntactic checking (during annotation) so it could only be caused by a single SQL statement being executed somewhere.

As far as I know, there is no table/view/procedural object with a name like "requrest_queue" anywhere in SQL Anywhere so I have no reason to believe this is any internally used/managed object. A malformed string passed to an Execute Immediate might be able to randomly produce such a string ... but that would still need to be identified from a request log.

Of course, the biggest concern is not these skipped operations, but what those operations should have been and whether or not that represents a loss of data/synchronicity.

Best of luck.

[VolkerBarth]

I would recommend look into triggers defined on that object and any procedures, functions, or other table object referenced there (and triggers on any table objects referenced by/cascaded from those ... etc. )

... or procedures/functions used in check constraints on that object or referenced objects...

[VolkerBarth]

Just another hint: When the same statement succeeds when run manually but fails within SQL Remote, I suspect it has to do with a trigger or procedure with code based on current remote user conditions or the like, or with a different user account used with SQL Remote...