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?

[Breck_Carter]

The message was "table not found" so it's not surprising that sysobjects doesn't contain it [snork].

Look for references inside all the stored procedures and triggers

BEGIN
SELECT * FROM SYSTABLE     WHERE view_def     LIKE '%request\\_queue%' ESCAPE '\\';
SELECT * FROM SYSPROCEDURE WHERE proc_defn    LIKE '%request\\_queue%' ESCAPE '\\';
SELECT * FROM SYSTRIGGER   WHERE trigger_defn LIKE '%request\\_queue%' ESCAPE '\\';
SELECT * FROM SYSEVENT     WHERE source       LIKE '%request\\_queue%' ESCAPE '\\';
END;

Request_queue may be a reference to an application table left over from an old (circa 2000) technique used with SQL Remote; see slide 56 of Rob Waywell's PPT here. ...perhaps with ssremote.

[VolkerBarth]

Request_queue may be a reference to an application table left over from an old (circa 2000) technique used with SQL Remote; see slide 56 of Rob Waywell's PPT here. ...perhaps with ssremote.

FWIW: Is that the same technique used with MobiLink, as presented by David Fishburn around 2002 (see here, note, that's a link within our friendly forum...)?