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?

asked 25 Mar '13, 08:01

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

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

(25 Mar '13, 09:08) Breck Carter
Replies hidden

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

(25 Mar '13, 09:21) Volker Barth

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.

permanent link

answered 26 Nov '15, 14:38

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 27 Nov '15, 08:00

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.

permanent link

answered 25 Mar '13, 09:17

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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:

×63
×61
×48

question asked: 25 Mar '13, 08:01

question was seen: 811 times

last updated: 27 Nov '15, 08:02