on 2011 Jan 31 8:41 PM
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
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Adam,
Did you try disconnecting and reconnecting before retrying the query? I indicated above that you may need to disconnect and reconnect after executing the alter statement. Disconnecting and reconnecting will drop all remote connections for the current connection. Doing so is similar to Volker's suggestion of performing an alter server ... connection close.
yea - disconnected. What Im seeing with proxy tables is quite weird. We have sql (in C code) as: EXEC SQL SELECT Count(*) INTO :Match_Count FROM xx WHERE lastnamekey1 = :lastname_key_1 AND firstnamekey1 = :firstname_key_1 AND Plan_ID = :Plan_UniqueID;
and this comes over to sql server as: SELECT t1.LastNameKey1 , t1.FirstNameKey1 , t1.FirstNameKey2 , t1.PLAN_ID FROM devtest.xx t1
Completely devoid of any where clause - and the cursor then runs for about two hours as it is a large table. this is quite off the where clause isnt used.
@Adam. This seems like a separate issue from the update problem that you were originally seeing. Can I then assume that setting the capability bit resolved the original update issue? In the select count(*) case, the query is being processed in no passthru. I will have a look and see why the query is in no passthru and then post back once I have a better idea.
@Adam. I cannot seem to get my server to process the query in no passthru. All of my tests suggest the query is processed in full passthru. Can you set cis_option to 7 and then post the results of the query decomposition. Note that the results of the decomposition will be displayed in the server window. I need all of the output starting with "The query is being processed in ??? mode" and ending with the full contents of "The Remote Statement for vt_1 is...".
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 ascI. 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 ascI. 02.02 09:35:21. Server SVR_MS:
UPDATE "Northwind".dbo."Orders"
SET "ShipAddress" = t1."ShipAddress" + ?
FROM "Northwind".dbo."Orders" t1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Volker,
The virtual statement is the statement that SA executes locally after the query decomposition has been completed. The statement the remote server sees is the UPDATE "Northwind".dbo."Orders" one.
Karim
@Adam: I'd generally agree with your reasoning, and I'm about to ask that as a separate question. As you see, SA simply does send the query without the ORDER BY to MS and does not rewrite it (as I would have expected). - But in this particular case, IMHO the semantics of the query are the same w/o ORDER BY. (The semantics might be different for other cases of UPDATE with ORDER BY.)
@Adam: Yes, I completely ignored the TOP clause for two reasons: First, as stated, I tested with MS SQL 2000, and that old version doesn't support UPDATE TOP at all. Second, UPDATE TOP .. ORDER BY is not supported with newer MS versions, either, and MS will choose random rows as the "TOP" ones. Therefore, when ignoring the TOP clause, the omitted ORDER BY should not make a difference. However, when using TOP without ORDER BY, MS would possily choose a different row set each time, and then the semnatics would certainly be different. - And now the iAnywhere engineers should help to clarify:)
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The hope we had with all of this was in order to prevent a full migration - that proxy tables would give us the ability to not have to port directly to sql server and save time on a huge project. We have another team working on a sql server migration - but wanted to see if this route would really cut the time down. This 'may' still work - I've resolved a bunch of issues so far by about four search and replace operations and server capabilities. My current task is to determine these issues and decide if this is feasible - Im not 100% beaten on this yet : )
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.