We upgraded FME from 2022.2.2 to 2024.0.2.2 one week ago, and I found out today the SQLExecutor gives an error for the SQL Server T-SQL Merger statement.  The SQLExecutor is set up as follows:

----------------------------------------------------------------------------------------------------------

FME_SQL_DELIMITER ;

use VENDOR_GLOBAL_SPGI;

MERGE INTO SPGI_INT.BLOCKS_VALID_INT AS t
USING (select distinct GA_ID,BLOCK_NAME,ONS_OFFSHORE from SPGI_INT.BLOCKS_VALID) AS s
ON t.GA_ID = s.GA_ID and t.BLOCK_NAME = s.BLOCK_NAME
WHEN MATCHED THEN
    UPDATE SET t.ONS_OFFSHORE = S.ONS_OFFSHORE;

--------------------------------------------------------------------------------------------------------

It gives this error:

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `MERGE INTO SPGI_INT.BLOCKS_VALID_INT AS t
USING (select distinct GA_ID,BLOCK_NAME,ONS_OFFSHORE from SPGI_INT.BLOCKS_VALID) AS s
ON t.GA_ID = s.GA_ID and t.BLOCK_NAME = s.BLOCK_NAME
WHEN MATCHED THEN
    UPDATE SET t.ONS_OFFSHORE = S.ONS_OFFSHORE'. Provider error `(-2147217900) A MERGE statement must be terminated by a semi-colon (;).'
An error has occurred. Check the logfile above for details
 

However, the Update and Delete statements work fine. For example, this sql works fine.

---------------------------------------------------------------------------------------------------

DELETE E
    FROM SPGI_INT.APPLICATIONS_VALID E
    INNER JOIN
    (
        SELECT objectid,
               ROW_NUMBER() OVER (PARTITION BY GA_ID order by date_of_last_load desc) as rank
        FROM SPGI_INT.APPLICATIONS_VALID
    ) T ON E.objectid = T.objectid
    WHERE rank > 1;

----------------------------------------------------------------------------------------------

Could this be a bug in 2024.0.2.2? I don’t think we had this issue in 2022.2.2.

Allen (allen.guan@woodside.com)