Ask for default server capabilities for newer remote server versions

[VolkerBarth]

As Karim has explained here, the default server capabilities for a particular server class are based on the oldest supported version of the according DBMS.

For example, for Microsoft SQL Server that is version 6.5, Service Pack 4 - released around 1998, methinks.

That's alright with me.

However, as these versions are often really old, it would be nice if SQL Anywhere could provide information for the default capabilities of newer versions. - I don't expect different server classes but would think of a document with capabilities that could be set for newer versions (or a bunch of scripts to update them?).

At the moment, one is somewhat on his own to find out whether a particular version might support more than the default capabilities, and that's a risky task not everyone likes to do. But relying on the defaults of an old version will often prevent efficient remote access and lead to unnecessary local computations.

I feel a somewhat "official" list would make updating the capabilities much more easy.

[VolkerBarth]

Just ran into the same issues again, as a complex remote statement run against a MS SQL Server 2014 SP2 server suddenly returns -660 error ("Connection is busy with results for another hstmt").

While trying to debug that with CIS_OPTION = 7, I notice that the statement is run in no passthrough mode because it contains SQL functions/expressions like isnull, coalesce, case, ..., which are all available with SQL Server. So I enabled them (at least for testing purposes) via "ALTER SERVER ... CAPABILITY 'isnull' ON;" and the like.

---

Unfortunately, that does not yet solve the problem, I'm still trying to figure out whether it's due to SP2 (which was installed some weeks ago) or the actual data... - guess I'll have to make it "simpler" for MS SQL Server and make more processing lateron on the imported data within the good engine:)

[VolkerBarth]

FWIW, setting "MARS_Connection=Yes" for the remote MS SQL Server solved the case. - I'm glad that SQL Anywhere allows for multiple active result sets on the same connection by default...:)

[Former Member · Accepted solution]

Volker,

You are correct. It has been some time since the default capability bits for the various SA remote servers has been updated. And as you pointed out, in addition to the bit settings being somewhat out of date, the list of capability bits and their meanings is not documented very well. I will open two enhancement requests to:

1) revisit the default settings for the various remote servers and get the settings updated to reflect the current set of released versions for each remote server class

and

2) get the set of capability bits, their meanings and their default setting properly documented.

Karim

[VolkerBarth · Accepted solution]

That's great news - thanks:)

[VolkerBarth · Accepted solution]

As we have switched over to MS SQL Server 2008 R2 lately, are there already any new lists available?

[VolkerBarth · Accepted solution]

Just as a refresh: I'm currently trying to use proxy tables with MS SQL 2008 R2, and when joining between a few of them, it often (and unnecessarily) falls back to "The query is being processed in NO PASSTHRU mode" - even though the original statement does work with MS SQL, too.

I'd tried to set the following cap bits (as they seem to get checked as not set):

ALTER SERVER MS_TEST
CAPABILITY 'Full outer joins' ON;
ALTER SERVER MS_TEST
CAPABILITY 'Unrestricted ANSI ON' ON;
ALTER SERVER MS_TEST
CAPABILITY 'Case expression' ON;
ALTER SERVER MS_TEST
CAPABILITY 'Derived tables' ON;

But I do not know their exact meaning, and additionally are not aware whether that MS SQL Server version does fullfil all requirements, so that's basically a wild guessing...

---

Resume: At least a better documentation and a list for current remote server versions would be highly appreciated. As stated, I don't ask for the default caps to be adapted within the software, a mere list would be sufficient...

[Former Member · Accepted solution]

Hi Volker,

I don't think it's a good idea to turn on server capabilities you're not completely aware of. For your list, all except 'Unrestricted ANSI ON' can be looked up as SQL Anywhere online help entries, and switching the capability on imo means that the back-end supports the same syntax with the same semantics.

• http://dcx.sap.com/index.html#sqla170/en/html/818ca3c76ce210149877bae7c7296362.html
• http://dcx.sap.com/index.html#sqla170/en/html/816a75596ce21014a014daafb8ac790c.html
• http://dcx.sap.com/index.html#sqla170/en/html/3be4b5016c5f10149f10a6db96ba7e52.html

What could happen if you set them (not claiming completeness):

1. (best) They are actually supported and work semantically equivalent to SQL Anywhere.

2. They are not supported and pushing a statement to the remote server raises an exception.

3. There is a syntactically identical but semantically different construct that always returns a different result.

4. (worst) There is a syntactically identical but semantically different construct that sometimes returns a different result.

An example for (4.) that I happen to have some experience with is 'Like - TSQL', which means that the back-end LIKE operator supports the same patterns as T-SQL (including simple reg.exp.s, which is not ANSI standard SQL). If you use a pattern with only the standard like patterns _ and %, everything will work fine, but if you use one with a simple reg.exp. and the back-end does not interpret it the same way, you'll usually get a different result (but no error since it still is a syntactically correct search pattern).

No need to say that I support any request for more and more detailed official documentation on such topics.

HTH

Volker Stöffler DB-TecKnowledgy

[VolkerBarth · Accepted solution]

I don't think it's a good idea to turn on server capabilities you're not completely aware of.

Yes, I certainly agree. As stated (note, it's an old posting from 2013), I just tried to figure out what exactly prevented the joins from being passed through, so that was just during tests.

---

BTW: Welcome here - I adjusted your links, this forum does not expect square brackets around URLs:)