Search code examples
sqlsybasepolybase

Polybase converting sql to ansi sql for a sybase connection

I have a polybase connection to a sybase server.

On my external tables I can not use the top command because poly-base converts it to limit

Example: 

SELECT TOP (1000) *
FROM [polygrab].[dbo].[ExtEvent_test]

Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [SAP][ODBC Driver][SQL Anywhere]Syntax error near 'limit' on line 3 .

I also have this server as a linked server and the top commands are supported via SAOLEDB.17 provider. However I can't figure out if this is the issue or how to change it.

My External Data connection is as follows: 

CREATE EXTERNAL DATA SOURCE [TicketMaster] WITH (
    LOCATION = N'odbc://externalserver.com:50459'
    , CONNECTION_OPTIONS = 'DRIVER={SQL Anywhere 17};
    Host=externalserver.com:50459; 
    Provider = ''SAOLEDB.17'';
    ServerName= <servername>;
    DatabaseName=<databasename>;'
    , CREDENTIAL = [PolyFriend2]
    )
Solution

  • Spoke to some Microsoft people working with the PolyBase feature.

    When you create an external data source, by default PUSHDOWN is ON. The problem you run into is that the pushdown capability for generic ODBC data sources is in an early stage, and the generated SQL may not be in an expected dialect by the source. Which seems to be the case here.

    So try an create the data source with PUSHDOWN = OFF, and see if it helps. Alternatively you can disable the pushdown in your query with an option: OPTION (DISABLE EXTERNAL PUSHDOWN). So something like:

    SELECT TOP (1000) *
FROM [polygrab].[dbo].[ExtEvent_test]
OPTION (DISABLE EXTERNAL PUSHDOWN);

    Check and see whether this works.