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)
