SQL Server error with Linked Server to Simba Presto

Image created with AI by author

I was using a MS SQL Server database that was linked to a SAP Hana database via a Simba Presto driver.

I had this query:

-- Does NOT work
SELECT *
FROM OPENQUERY(LINKED_SERVER_NAME,'
    SELECT fieldName
    FROM myTable
    WHERE refresh_date = ( 
        SELECT MAX(refresh_date) AS maxRefreshDate FROM myTable 
    )
    GROUP BY fieldName
')

I was getting this error:

OLE DB provider “MSDASQL” for linked server LINKED_SERVER_NAME returned message “[Simba][Presto] (1060) Presto Query Error: Invalid format: “Not Specified” (7)”.

The values are “Yes” and “No”. I tried a handful of things including CASTing it as a VARCHAR, and using the SUBSTRING function to take only the first character (figuring I can at least use a “Y” or “N”).

I’m not sure why this works, but the solution was to derive the “maximum refresh date” in a separate query.

This works:

-- Does work
DECLARE @maxRefreshDate VARCHAR(10) = (
    SELECT * FROM OPENQUERY(LINKED_SERVER_NAME,'
      SELECT MAX(refresh_date) AS maxRefreshDate FROM myTable
    ')
);

DECLARE @qry NVARCHAR(4000) = N'
    SELECT fieldName
    FROM myTable
    WHERE refresh_date = DATE(''''' + @maxRefreshDate + ''''' )
    GROUP BY fieldName
';

DECLARE @SQL NVARCHAR(4000) = N'
    SELECT * FROM OPENQUERY(LINKED_SERVER_NAME, ''' + @qry + ''')
';

PRINT @SQL
EXEC sp_executesql @SQL;