How to modify the "read only" property of a remote server?

[VolkerBarth]

Using v12.0.1.3817, I have created a remote server with READ ONLY and would like to change the afterwards.

The syntax of ALTER SERVER does not seem to allow for that, and just restating the original definition (i.e. simply replacing "CREATE SERVER" with "ALTER SERVER" and omitting the READ ONLY clause) does not change that property, either.

So I suspect I have to drop the remote server and create it again - and therefore have to drop and recreate all according remote tables? - Sigh.

Question: Or is there a way to modify this property without dropping/recreating?

[Breck_Carter]

Interesting... the READ ONLY clause isn't described, just shown in the syntax.

[VolkerBarth]

the READ ONLY clause isn't described, just shown in the syntax

You're relating to CREATE SERVER, aren't you? FWIW, it's not described but surely does have the expected effect:

Trying to modify the contents of a proxy table on a READ ONLY server issues a -658 error ("Remote server '%1' is currently configured as read-only").

---

In contrast, ALTER SERVER does not allow that clause:

ALTER SERVER SVR_TEST
CLASS 'mssodbc'
USING 'MS_TEST'
READ ONLY;

issues a -131 sintax error for "READ".

[VolkerBarth · Accepted solution]

FWIW, v17 has enhanced the ALTER SERVER statement to modify the READ ONLY state with the according clause:

ALTER [ REMOTE ] SERVER server-name
...
[ READ ONLY [ ON | OFF | VALUE variable ] ]

It's not mentioned in the What's New section but apparently is has been enhanced compared to v12/v16. It's also mentioned in the "What's New" section:

Enhancements to remote servers (database upgrade required)
Support for altering a remote server to be read-only, using the new READ ONLY clause of the ALTER SERVER statement.

[Former Member]

You are correct that you cannot use the ALTER SERVER statement to change a remote server from read only to non-read only and vice versa. I would categorize what you have found as a bug and will open a bug report to get the problem fixed.