We have the following update on SQL Anywhere 11 which is failing with the message

"Update operation attempted on non-updateable remote query"

It is

UPDATE mylocaltable
   SET mycol = '1'
 WHERE mycol = '2'
   AND mykeyname IN ( SELECT mykeyname in FROM myproxytable where proxycol = '1' )

mylocaltable is owned by my userid, myproxytable is owned by my userid and is an updateable proxy on a MS SQL database.

Why is this failing and is there a way around it?

We believe when we first tested this SQL it worked but don't know if anything has changed.

asked 16 Aug '12, 21:44

Glenn%20Barber's gravatar image

Glenn Barber
1.1k234051
accept rate: 0%

edited 17 Aug '12, 02:54

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270

What build number of SQL Anywhere 11 are you using?

(17 Aug '12, 02:56) Mark Culp
Replies hidden

Hi Mark We are using 11.0.1.2436 - today I will EBF update to 2837

(20 Aug '12, 19:08) Glenn Barber

FWIW, "Remote data access debugging" (aka setting the CIS_OPTION) might give a clue what part of the statement is sent to the remote server...

I remember similar problems, when a local update based on a join with remote tables did not work, and we had to use a local temporary table to "cache" the remote data and then join with that - cf. this particular question. - Note: I don't claim that workaround is also necessary in your sample...that will depend on how the statement is rewritten...

permanent link

answered 17 Aug '12, 03:15

Volker%20Barth's gravatar image

Volker Barth
30.6k305455662
accept rate: 32%

I was able to get this to work by inserting into the local SA temp tables and doing the update with the temp tables. However the MS SQL DBA says that we are leaving locked work tables in the MS SQL environment - although I am doing both a commit and a disconnect in the app.

(20 Aug '12, 19:11) Glenn Barber
Replies hidden

Then it seems like you were having a similar problem like my cited one - possibly as the IN subquery may be rewritten as a join or the like...

I can't comment on the MS SQL tables being locked - would a final ALTER SERVER ... CONNECTION CLOSE statement help?

(21 Aug '12, 02:57) Volker Barth
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

question asked: 16 Aug '12, 21:44

question was seen: 848 times

last updated: 21 Aug '12, 02:57