Why does local update fail when it depends on proxy

[glenn_barber]

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.

[MarkCulp]

What build number of SQL Anywhere 11 are you using?

[glenn_barber]

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

[VolkerBarth]

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

[glenn_barber]

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.

[VolkerBarth]

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?