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_SQLA_member1694883 · Accepted solution]

Thank you everyone. After translating the log file, and from the helpful comments, answers and suggestions, we have now identified that this is being caused by a trigger calling a stored procedure under some rare circumstances, which references the non-existent 'request_queue' table. As suggested, it was being caused by a trigger calling a stored procedure. Both were written around mid-2001 by the original developers who have long since left the project. We now trying to work out why the trigger and stored procedure were included at all, as they don't appear to be required for anything. It does look similar to what Breck suggests as an old, early 2000s, technique, similar to Rob Waywell's presentation.

[VolkerBarth · Accepted solution]

FWIW, Robert, I guess you have actually given an answer to your question, and so I converted your comment into one.

Glad you could work it out:)

[VolkerBarth · Accepted solution]

We now trying to work out why the trigger and stored procedure were included at all.

Probably the original developers missed to deploy the final "drop the request_queue" command to that queue:)