We have a system that needs to have some tables on sql server now. Previously it was in an ASA 9 db. We were hoping proxy tables would allow us to use the same syntax, however this query which worked in our Asa database now fails when it seems ASA just sends the query over: Update top 1 XXX where yyy = 0 order by unique_id desc Sql Server fails on this query because of the order by (and the top 1 syntax - should be top(1) for update on sql server) Does anyone know if this changed in more recent versions (I realize ASA 9 is quite old) or is there some other option that can be set without rewriting the query? Our hope was that with proxy tables we could keep the same syntax when passing the query to ASA but this seems to not be the case. Thanks for any info! Adam |
Not as an actual answer, but you might turn on "Proxy debugging" by
Then you will see in the server's window (or better: the dbsrv9 -o Log.txt) what exactly the engine sends to the remote server. That really helps finding out such issues (and understanding what parts of a query are processed locally vs. remote). Cf. the docs on Remote data access troubleshooting. Thanks - this shows the mode is full passthrough and it just sends the full query over to sql server. Im looking at the syntax at how I can change this just for this update syntax, but haven't quite figured this out yet. What about "First" instead of "Top 1"? (Not sure about MS SQL syntax here) Hi Volker, thanks for the input. The issue is we're trying to maintain 100% compatibility in the queries (that was the hope with the proxy tables anyways). Sql Server will support top (1), but we want to try to avoid changes. |
According to the MS SQL 2005/2008 docs, UPDATE does not support an ORDER BY, and TOP (n) will just choose a random selection (which is a surprising shortcoming IMHO...). Therefore, in case you need an ordered UPDATE, you might change the statement to use an ordered subselect, such as
That should be compatible, and with select, TOP doesn't require the parantheses around its arguments. (CAVEAT: I have not tested that myself). we have a fix for the queries (just using where id = (select top 1)) but we were hoping with this to not have to make any query changes. It may be impossible since even if the 'order by' was dropped off - the behavior would now be different than the default sql server update since we are not limiting by 'top X order by' anymore |
Also I was thinking I could set: SET OPTION public.Ansi_update_constraints = 'strict'; to help find these locations in that application - but it still just sends the query over to sql server without raising an error on the ASA side.
@Adam: What exact error message do you get for this UPDATE statement?
Hi Volker - it is "Incorrect syntax near the keyword 'ORDER'."