cancel
Showing results for 
Search instead for 
Did you mean: 

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

VolkerBarth
Contributor
2,627

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?

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

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.

Answers (1)

Answers (1)

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.