Behaviour of SQL Server Proxy tables using IF Exists

[Former Member]

I have a Proxy Table from a SQL Server data base that I am trying to execute a IF EXISTS (SELECT 1 FROM RemoteTable) RETURN 0 ELSE 0

This was working with SQLAnywhere 11 and now with SQLAnywhere 16.0.0.2043 this fails because the generated statement that gets to the remote server is

SELECT FIRST 1 FROM RemoteTable

The issue is the FIRST that is thrown into the statement. This was reported for ORACLE proxy tables last october but I couldn't find any place where it had been corrected or reported for SQL SErver.

[Former Member]

SELECT 1 or SELECT (*) both issue the SELECT FIRST syntax.

I've settled on the rather crude return (coalesce (select 1 from RemoteTable, 0)) which works because the problem is with the IF EXISTS statement

I primarily wanted to let SAP know its a problem for SQL Server as well as Oracle.

[Former Member]

This problem has been fixed in SQL Anywhere 16.0.0 build 2048 an up.

[VolkerBarth]

The according CR #764386 link shows that:

    ================(Build #2048  - Engineering Case #764386)================

    If an application executed a query against a Microsoft SQL Server proxy table 
    that contained SELECT FIRST or a subquery in an IF EXISTS( … ), then there 
    was a chance the Remote Data Access layer would incorrectly send the SELECT 
    FIRST to the remote server. Note that a similar problem existed with remote 
    Oracle servers as well. These problems have now been fixed and the Remote 
    Data Access layer will now send a TOP 1 instead.