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. |
This problem has been fixed in SQL Anywhere 16.0.0 build 2048 an up. 1
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.
(21 Jul '15, 07:40)
Volker Barth
|
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.