Adam,

You should be able to force SA to not send over an "order by update" to the remote server by executing the following:

ALTER SERVER rem CAPABILITY 'Order by allowed in update' OFF

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.

Karim

Some tests based on Karim's suggestion:

I'm testing with SA 11.0.1.2427 and MS SQL 2000 SP4 (don't have a newer version) and the default Northwind db.

' SVR_MS is a default type MSSODBC server with write access

create existing table FT_NorthwindOrders at 'SVR_MS.Northwind.dbo.Orders';
select * from FT_NorthwindOrders; -- works

-- update with TOP fails because MS SQL 2000 doesn't support it (2005 and up do)
-- error: "Incorrect syntax near the keyword 'TOP'"
update top 10 FT_NorthwindOrders
set ShipAddress = ShipAddress + ' - My SA Test'
order by OrderID;

-- the same with top (10)
update top (10) FT_NorthwindOrders
set ShipAddress = ShipAddress + ' - My SA Test'
order by OrderID;

-- update with ORDER BY fails by default
-- error: "Incorrect syntax near the keyword 'ORDER'"
update FT_NorthwindOrders
set ShipAddress = ShipAddress + ' - My SA Test'
order by OrderID;

alter server SVR_MS capability 'Order by allowed in update' off

-- update with ORDER BY still fails with the same error
update FT_NorthwindOrders
set ShipAddress = ShipAddress + ' - My SA Test'
order by OrderID;

-- but after dropping the connection, it works
alter server SVR_MS connection close

-- succeeds now
update FT_NorthwindOrders
set ShipAddress = ShipAddress + ' - My SA Test'
order by OrderID;

drop table FT_NorthwindOrders;

Resume:

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):

I. 02.02 09:35:21. The query is being processed in FULL PASSTHRU mode

I. 02.02 09:35:21. The Original Statement is
update FT_NorthwindOrders
set FT_NorthwindOrders.ShipAddress = FT_NorthwindOrders.ShipAddress+' - My SA Test'
from FT_NorthwindOrders
order by FT_NorthwindOrders.OrderID asc

I. 02.02 09:35:21. The Virtual Statement is
update FT_NorthwindOrders
set FT_NorthwindOrders.ShipAddress = FT_NorthwindOrders.ShipAddress+' - My SA Test'
from FT_NorthwindOrders
order by FT_NorthwindOrders.OrderID asc

I. 02.02 09:35:21. Server SVR_MS:
UPDATE "Northwind".dbo."Orders"
SET "ShipAddress" = t1."ShipAddress" + ?
FROM "Northwind".dbo."Orders" t1

Not as an actual answer, but you might turn on "Proxy debugging" by

SET OPTION public.cis_option = 7;

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.

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

update XXX
set ccc == vvv
from (select top 1 unique_id from XXX order by unique_id desc) as XXX_ordered
where yyy = 0 and XXX.unique_id = XXX_ordered.unique_id

That should be compatible, and with select, TOP doesn't require the parantheses around its arguments. (CAVEAT: I have not tested that myself).

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.