on 2013 Mar 25 8:01 AM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
82 | |
29 | |
9 | |
8 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.