The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 31 Jan '11, 20:41

Adam's gravatar image

Adam
19335
accept rate: 0%

edited 07 Feb '11, 14:39

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646

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.

(31 Jan '11, 20:52) Adam

@Adam: What exact error message do you get for this UPDATE statement?

(31 Jan '11, 21:10) Volker Barth

Hi Volker - it is "Incorrect syntax near the keyword 'ORDER'."

(02 Feb '11, 05:52) Adam

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

permanent link

answered 01 Feb '11, 12:39

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

nothing different in the behavior unfortunately

(02 Feb '11, 05:50) Adam

See my tests as a separate answer (just because of the need to format...)

(02 Feb '11, 08:45) Volker Barth

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.

(02 Feb '11, 13:13) Karim Khamis

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.

(03 Feb '11, 07:25) Adam

@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.

(03 Feb '11, 12:24) Karim Khamis

@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...".

(03 Feb '11, 12:38) Karim Khamis
More comments hidden
showing 5 of 6 show all flat view

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

permanent link

answered 02 Feb '11, 08:44

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 02 Feb '11, 11:41

1

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

(02 Feb '11, 13:15) Karim Khamis

If the 'order by' isnt passed over when its not supported - then isn't the result of the query different when executed on sql server vs. SQLA, or is SQLA smart enough to re-do the query with a subquery select?

(04 Feb '11, 17:35) Adam

@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.)

(04 Feb '11, 21:08) Volker Barth

Hmm.. I dont think I follow - how are the semantics of the order by missing the same (if we're using top X) - I assume you meant the semantics are the same without the top X?

(04 Feb '11, 22:44) Adam

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

(05 Feb '11, 22:14) Volker Barth

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.

permanent link

answered 31 Jan '11, 20:58

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

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.

(31 Jan '11, 21:23) Adam

What about "First" instead of "Top 1"? (Not sure about MS SQL syntax here)

(31 Jan '11, 21:54) Volker Barth

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.

(01 Feb '11, 05:05) Adam

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

permanent link

answered 01 Feb '11, 09:03

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

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

(02 Feb '11, 05:55) Adam

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.

permanent link

answered 08 Feb '11, 09:31

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

1

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

(08 Feb '11, 16:29) Adam
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×62
×28

question asked: 31 Jan '11, 20:41

question was seen: 1,913 times

last updated: 08 Feb '11, 09:31