Error during replication "Table 'request_queue' not found"

[former_SQLA_member1694883]

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:)

[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...

[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...)?