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?

asked 10 Mar, 18:00

Baron's gravatar image

Baron
1.3k5279114
accept rate: 37%

1

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.

(11 Mar, 05:02) Volker Barth
Replies hidden

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

(11 Mar, 08:50) Baron

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)

(11 Mar, 08:52) Baron

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

(11 Mar, 09:46) Volker Barth
2

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.

(11 Mar, 09:54) Reg Domaratzki

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

(11 Mar, 10:05) Volker Barth
showing 1 of 6 show all flat view

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.

permanent link

answered 11 Mar, 09:44

Reg%20Domaratzki's gravatar image

Reg Domaratzki
6.8k339105
accept rate: 39%

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.

(11 Mar, 09:51) Baron

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

(11 Mar, 10:10) Volker Barth
Replies hidden
1

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

(11 Mar, 10:17) Reg Domaratzki
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×83
×66

question asked: 10 Mar, 18:00

question was seen: 315 times

last updated: 11 Mar, 10:29