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
You should be able to force SA to not send over an "order by update" to the remote server by executing the following:
where "rem" is the remote server name that you used when you executed CREATE SERVER. Note that you may have to disconnect and reconnect after executing the alter server to ensure the new setting takes effect.
See if that does the trick. If it does, then I will open a bug case to make sure SA does not automatically set that capability bit for SQL Server.
answered 01 Feb '11, 12:39
Some tests based on Karim's suggestion:
I'm testing with SA 188.8.131.527 and MS SQL 2000 SP4 (don't have a newer version) and the default Northwind db.
I'm guessing this capability should be OFF by default for the MSSODBC server type.
Interestingly enough, the cis_option debut output doesn't clearly tell that the ORDER BY is stripped from the remote statement (at least for my eyes - I would have expected that in the virtual statement):
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.
answered 31 Jan '11, 20:58
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).
answered 01 Feb '11, 09:03
Adam, just as a completely different approach:
As you seem to run into several problems with syntax differences between both DBMS - would it be possible to change the database binding directly from SA to MS SQL? - I.e. to access a MS SQL database directly and not via remote data access.
I have never worked with MS SQL and Embedded SQL (and don't know whether this API exists at all for MS SQL). But with classic call level interfaces like ODBC, you can usually write application code that runs both against SA and MS SQL.
E.g., we have ported applications written for MS SQL to SA years ago, so that these apps could run with both database systems.
I have to admit that it was easier to port from MS SQL to SA because SA supports lots of MS SQL syntax because of its builtin T-SQL compatibility. In constrast, when using lots of SA's syntax extensions or ANSI syntax, a port to MS SQL might be more difficult.
However, a complete port will prevent you from all those "Why isn't this query run in full passthrough mode and taking sooooooo long?" questions.
answered 08 Feb '11, 09:31