Question Re: Synchronize Subscription (DBREMOTE SQL17) in Technology Q&A

Synchronize Subscription (DBREMOTE SQL17)

Baron — Tue, 10 Mar 2020 18:00:51 GMT

We were using SQL REMOTE for years on SQL Anywhere 10 without problems. From some weeks we upgraded to SQL Anywhere 17, and I tried to synchronize a subscription from Server to a Remote. One of the tables has reversed names as column name (ID, DESCRIPTION, DATETIME), and the synchronization does not work anymore!!

Is the DBREMOTE now more sensitive? Is there a workaround for this problem?

Re: Synchronize Subscription (DBREMOTE SQL17)

VolkerBarth — Wed, 11 Mar 2020 05:02:38 GMT

and the synchronization does not work anymore!!

What errors etc. do you get?

FWIW, we have used SQL Remote from v5.5 to v17 and don't have had problems when migrating to newer versions (or if so, this was solved via EBFs) but we do not use column names with reserved names.

Re: Synchronize Subscription (DBREMOTE SQL17)

Baron — Wed, 11 Mar 2020 08:50:14 GMT

I get these error messages:

E. 2020-03-10 13:31:19. SQL statement failed: (-131) Syntax error near ',' on line 1

E. 2020-03-10 13:31:19. Skipping:

E. 2020-03-10 13:31:19. SELECT Col1, Description, Col3, row, Col5, Col6, Col7 FROM Table1

E. 2020-03-10 13:32:08. Receiving messages failed

Re: Synchronize Subscription (DBREMOTE SQL17)

Baron — Wed, 11 Mar 2020 08:52:54 GMT

Yes I know it is very bad to name columns with reserved names. But I can see such mistakes even with system tables!!

ml_table for example (mobilink)

Re: Synchronize Subscription (DBREMOTE SQL17)

regdomaratzki — Wed, 11 Mar 2020 09:44:36 GMT

I'm not having any issues performing either regular replication of operations on a table with columns named row and description, or executing a synchronize subscription on this table.

I. 2020-03-11 09:23:26. SQL Remote Message Agent Version 17.0.10.6057
I. 2020-03-11 09:23:26. Received message from "cons" (0-0000708956-0000709293-0)
I. 2020-03-11 09:23:26. Applying message from "cons" (0-0000708956-0000709293-0)
I. 2020-03-11 09:23:26. INSERT INTO cons.admin(a_pkey,"row",description)
                        VALUES (21,'after','synch')
I. 2020-03-11 09:23:26. COMMIT

I. 2020-03-11 09:23:21. SQL Remote Message Agent Version 17.0.10.6057
I. 2020-03-11 09:23:21. Received message from "cons" (0-0000708743-0000708956-0)
I. 2020-03-11 09:23:21. Applying message from "cons" (0-0000708743-0000708956-0)
I. 2020-03-11 09:23:22. DELETE FROM cons.admin
I. 2020-03-11 09:23:22. SELECT a_pkey, "row", description FROM cons.admin
I. 2020-03-11 09:23:22. 40 rows synchronized
I. 2020-03-11 09:23:22. COMMIT

I did notice that in my output, the "row" column name was double quoted, but it wasn't in yours. Row was not a reserved word in v10, but it is in v17.

I suspect that you have upgraded the remote site to v17, but the consolidated database is still using v10. When v10 dbremote is generating messages, it does not know that "row" is a reserved word that needs quotes around it. You will need to upgrade the consolidated database to use v17 as well, or change your column name.

Reg

PS: In v12, we introduced the "reserved_keywords" option that could have forced a v12 dbremote to double quote any word you added to the reserved_keywords option, but that option does not exist in v10.

Re: Synchronize Subscription (DBREMOTE SQL17)

VolkerBarth — Wed, 11 Mar 2020 09:46:15 GMT

Hm, why does SQL Remote try to apply SELECT statements? Is this from passthrough sessions? In regular mode, it should only apply inserts/updates/deletes...

Re: Synchronize Subscription (DBREMOTE SQL17)

Baron — Wed, 11 Mar 2020 09:51:59 GMT

Yes, your suspection is correct!

My consolidate is still V10, and my remotes are V17!

We are now in migration phase, and Cons. will be converted later to V17.

Now is very difficult to change column name.

It is now clear what is the cause, since 'row' is for V10 still not reserved.

Re: Synchronize Subscription (DBREMOTE SQL17)

regdomaratzki — Wed, 11 Mar 2020 09:54:30 GMT

That is the output you will see in the SQL Remote log when the remote database is applying the results of a SYNCHRONIZE SUBSCRIPTION command. I posted a working example in my answer.

Re: Synchronize Subscription (DBREMOTE SQL17)

VolkerBarth — Wed, 11 Mar 2020 10:05:51 GMT

Ah, I see, I certainly should have better re-read the question...

Re: Synchronize Subscription (DBREMOTE SQL17)

VolkerBarth — Wed, 11 Mar 2020 10:10:12 GMT

You will need to upgrade the consolidated database to use v17 as well, or change your column name.

Or for the interim just re-extract remotes in case a SYNCHRONIZE SUBSCRIPTION is needed? (Unless you use that statement regularly, say, to add subscriptions to an existing remote).

Re: Synchronize Subscription (DBREMOTE SQL17)

regdomaratzki — Wed, 11 Mar 2020 10:17:07 GMT

v10 dbremote will not double quote "row" in regular insert/update/delete statements either, so I suspect these will fail in this environment as well. I don't think a re-extract will help unless there are typically no operations on that table in the consolidated database.

I. 2020-03-11 09:34:41. SQL Remote Message Agent Version 10.0.1.4310
I. 2020-03-11 09:34:41. Received message from "cons" (0-0000000000-0000361259-0)
I. 2020-03-11 09:34:41. Applying message from "cons" (0-0000000000-0000361259-0)
I. 2020-03-11 09:34:41. INSERT INTO cons.admin(a_pkey,row)
                        VALUES (1,'8.03681275')

Reg