We have the following update on SQL Anywhere 11 which is failing with the message
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.
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...
answered 17 Aug '12, 03:15